Is there a VBA code to delete all rows after the last row which contains data?

Question

I have an Excel table with data in columns A:Y. Columns A:G will always contain values, while columns H:Y MAY NOT contain values. I would like to 1) sort my table based on based on column "H" which will move the rows containing blanks to the bottom, and then 2) delete only those rows which do not contain data in columns H:Y.

The data table will be ever changing and growing, so static variables are not truly an option.

Table.Sort.SortFields.Add2 Key:=Table.Range("H3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With Table.Sort
    .SetRange Table.Range("A3:Y3" & LastRow)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

With Tab
    LastRowB = .Range("H" & .Rows.Count).End(xlUp).Row + 1
End With

Tabs.Rows(LastRowB).End(xlDown).Select
Selection.Clear

`Table.Range("A3:Y3" & LastRow)` should likely be `Table.Range("A3:Y" & LastRow)` — BigBen, Jun 13 '23 at 19:22
I'm not sure if it's still true, but it used to be that when you saved a spreadsheet it would automatically truncate all of the worksheets. — RBarryYoung, Jun 13 '23 at 19:33

Bill · Answer 1 · 2023-06-13T19:32:57.010

3

Of course after many hours of trying to figure this out, I get it right after I posted my question:).

Tab.Rows(LastRowB & ":" & Tab.Rows.Count).Delete

edited Jun 13 '23 at 19:32

answered Jun 13 '23 at 19:27

Bill

51
5

score 3 · Answer 2 · answered Jun 13 '23 at 19:34

Here is an alternative where you can delete the rows using a filter.

You can adapt it to fit your needs

Sub FilterDeleteAndSort()

    ' Define a variable and assign a table (listobject)
    Dim targetTable As ListObject
    Set targetTable = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")
    
    ' Filter and delete
    With targetTable
        If .Parent.FilterMode Then .Range.AutoFilter
        .Range.AutoFilter Field:=8, Criteria1:="" ' Field 8 (is column number in the table)
        .Range.AutoFilter Field:=9, Criteria1:=""
        .Range.AutoFilter Field:=10, Criteria1:=""
        .DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
        .Range.AutoFilter
    End With

    ' Sort the table
    targetTable.Sort.SortFields.Clear
    targetTable.Sort.SortFields.Add2 Key:=Range("H1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With targetTable.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Is there a VBA code to delete all rows after the last row which contains data?

2 Answers2