Removing lines with no data

Question

I've built many macros but haven't edited so much. I'm trying to filter a spreadsheet in column B for "0", then delete all rows. what's happening is if there are no rows containing "0" in column B, the code ends up deleting all the data I want to keep. My code is:

    ActiveSheet.ListObjects(1).Range.AutoFilter Field:=2, _
        Criteria1:="=0", Operator:=xlAnd
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.ListObjects(1).Range.AutoFilter Field:=2
    Columns("A:A").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ColumnWidth = 8.5
    ActiveWindow.LargeScroll ToRight:=-1
End Sub

Use `SpecialCells` like [this](https://stackoverflow.com/questions/17194394/vba-how-to-delete-filtered-rows-in-excel) — urdearboy, Apr 09 '20 at 15:24
Your title specifies **no data** but your code is filtering for **0** ..................which do you want?? — Gary's Student, Apr 09 '20 at 15:31

score 2 · Answer 1 · answered Apr 09 '20 at 15:38

Try this code

Sub Test()
    Call DelFilterParam(Sheet1, "Table1", 2, "=")
End Sub

Sub DelFilterParam(ByVal wsSheet As Worksheet, ByVal stTable As String, ByVal iField As Integer, ByVal vCriteria As Variant)
    With wsSheet
        With .ListObjects(stTable).DataBodyRange
            .AutoFilter
            .AutoFilter Field:=iField, Criteria1:=vCriteria
            .EntireRow.Delete
            .AutoFilter
        End With
    End With
End Sub

score 1 · Answer 2 · answered Oct 01 '20 at 02:23

You can try this code

 Sub test()
    Dim H As ListObject
    Set H = Sheet1.ListObjects(1)
    H.Parent.Activate
    H.AutoFilter.ShowAllData
    H.Range.AutoFilter Field:=2, Criteria1:="0"
    Application.DisplayAlerts = False
    H.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    H.AutoFilter.ShowAllData
End Sub

Removing lines with no data

2 Answers2