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

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.

CodePudding user response：

The following will create a table to fit the data assuming their 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)

CodePudding user response：

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

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

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