CODEWITHSUNDEEP
excelvba
David
David

Reputation: 1

Removing lines with no data

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

Upvotes: 0

Views: 66

Answers (2)

أبو عائشة ورقية ومحمد
أبو عائشة ورقية ومحمد

Reputation: 133

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

Upvotes: 1

YasserKhalil
YasserKhalil

Reputation: 9568

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

Upvotes: 2

Related Questions