Dynamically storing a cell reference as a variable in VBA to then select (and delete) a range using the variable stored

Question

I currently have a VBA macro that turns a regular data extract into a table. In the macro I have defined a range which is large enough to exceed the number of rows typically extracted.

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$AG$20000"), , xlYes).Name _
    = "Table1"

My macro then does some other transformation/addition of formulas etc to the table, and the table is then presented via PowerBI.

I want to delete the excess rows in the table - which varies for each extract.

In the example below - which has recorded the desired sequence of steps, there are only 186 rows.

Range("Table1[[#Headers],[Client Id]]").Select
Selection.End(xlDown).Select
Range("A187").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("187:20000").Select
Selection.Delete Shift:=xlUp

I want to store the range ("A187") as a variable I then want to insert the stored variable in the selection 187:20000

Alternatively, if I could do a variabilised selection of the range I want to turn into a table, that would work too.

Any help would be appreciated.

TinMan · Accepted Answer · 2022-10-18T10:55:21.373

3

The following will create a table to fit the data assuming there are no extra data cells:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "Table1"

If you need to force columns to include "A:AG" only use:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion.Columns("A:AG"), , xlYes).Name = "Table1"

ActiveSheet.ListObjects.Add(...).Name = "Table1" is how a recorded macro would create the table. To avoid naming conflict, I would avoid using the generic Table1 as a name.

If the name isn't important use:

ActiveSheet.ListObjects.Add xlSrcRange, Range("A1").CurrentRegion.Columns("A:AG"), , xlYes

If there is only one Table on the woksheet, you can refer to it as:

ActiveSheet.ListObjects(1)

edited Oct 18 '22 at 10:55

answered Oct 04 '22 at 22:09

TinMan

6,624
2
10
20

1

Or you can use `Dim lo as ListObject: Set lo = ActiveSheet.ListObjects.Add(...)` and then use `lo`, e.g. `lo.Name ="WhatEver"` – Ike Oct 05 '22 at 06:43
@Ike that is my preferred approach. – TinMan Oct 05 '22 at 08:33

score 0 · Answer 2 · answered Oct 04 '22 at 23:36

Delete Empty Bottom Rows (Applied to an Excel Table (`ListObject`))

Sub DeleteEmptyBottomRowsTEST()
     
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    Dim tbl As ListObject: Set tbl = ws.ListObjects("Table1")
    
    ' Remove possible filters.
    ' The Find method will fail if the table is filtered.
    If tbl.ShowAutoFilter Then
        If tbl.AutoFilter.FilterMode Then tbl.AutoFilter.ShowAllData
    End If
     
    Dim rg As Range: Set rg = tbl.DataBodyRange
    If rg Is Nothing Then Exit Sub
     
    DeleteEmptyBottomRows rg

End Sub

Sub DeleteEmptyBottomRows(ByVal rg As Range)
    
    Dim lCell As Range
    Set lCell = rg.Find("*", , xlFormulas, , xlByRows, xlPrevious)
    If lCell Is Nothing Then rg.Delete xlShiftUp: Exit Sub
    
    Dim rOffset As Long: rOffset = lCell.Row - rg.Row + 1
    Dim rCount As Long: rCount = rg.Rows.Count
    If rOffset = rCount Then Exit Sub
    
    Dim rResize As Long: rResize = rCount - rOffset
    rg.Resize(rResize).Offset(rOffset).Delete xlShiftUp

End Sub

Dynamically storing a cell reference as a variable in VBA to then select (and delete) a range using the variable stored

2 Answers2

Delete Empty Bottom Rows (Applied to an Excel Table (ListObject))

Delete Empty Bottom Rows (Applied to an Excel Table (`ListObject`))