CODEWITHSUNDEEP
excelvba
icalderon
icalderon

Reputation: 63

How to use If-Else statements to filter a table?

The code below filters the 4th column of Table1 for any blank cells then deletes those rows if blank. 

Sub DeleteJob()
    Dim tbl As ListObject
    Dim ws As Worksheet

    'Set reference to the sheet and Table.
    Set ws = Sheets("Line Item Summary")
    Set tbl = ws.ListObjects("Table1")
    ws.Activate

    'Clear any existing filters
    tbl.AutoFilter.ShowAllData

    '1. Apply Filter
    tbl.Range.AutoFilter Field:=4, Criteria1:=""

    '2. Delete Rows
    Application.DisplayAlerts = False

    tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete

    Application.DisplayAlerts = True

    '3. Clear Filter
    tbl.AutoFilter.ShowAllData
End Sub

When the code is run once, the rows with blanks are all removed and the 4th column is left with cells with values, but an error will pop up because there are no more blank cells to look for/delete. I was thinking of using an "If/Else" statement so if there are blanks then the respective row is deleted otherwise do nothing if all cells in the column have a value. I do not know how to modify the code below to accommodate for this.

'2. Delete Rows
Application.DisplayAlerts = False

tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete

Application.DisplayAlerts = True

Upvotes: 1

Views: 256

Answers (1)

BigBen
BigBen

Reputation: 50008

One option: use Application.CountBlank first before even filtering.

If Application.CountBlank(tbl.ListColumns(4).DataBodyRange) = 0 Then Exit Sub

Upvotes: 3

Related Questions