Problem with filters and entire row delete

Question

I have problem with Entirerow.Delete.
Sometimes my macro deletes all rows from the worksheet and sometimes it is going back to the last error handling and skipping order.

I tried to filter values, assign them to the range and delete the range, but it took about 20 minutes for program execution, so I had to kill processing.

wb_current.Worksheets("RH").Range("A6:V6").AutoFilter Field:=22, Criteria1:=Array("#N/A", ""), Operator:=xlFilterValues
With wb_current.Worksheets("RH")
    lng_last_row_main = .Cells(Rows.Count, 1).End(xlUp).Row
    If lng_last_row_main > 6 Then
        .Rows("7:" & lng_last_row_main).EntireRow.Delete
    End If
    .AutoFilterMode = False
    lng_last_row_main = .Cells(Rows.Count, 1).End(xlUp).Row
End With

I would like to have a filter set to show me rows with blank or NAs in specific column, then delete those rows containing those values, then set off filter and get table without blank rows.

Jan Fusik Jan Fusik · Accepted Answer · 2019-05-24T13:24:20

Aleksander, maybe I am able to help your case.

From my understanding of your problem and desired effect, is it really necessary to actually use filter? If you are using it just to be able to delete all blank rows in certain column, then a simple Loop with If function should be enough.

Please see my proposed code below:

Sub delete_blank_rows()
Dim lastRow As Long
Dim cel As Range

lastRow = ThisWorkbook.Worksheets("RH").Cells(Rows.Count, 1).End(xlUp).Row

'loop through every cell in column C until the last row (lastRow is taken from column A)
For Each cel In ThisWorkbook.Worksheets("RH").Range("C1:C" & lastRow)

    'function that evaluates if cell in column C is blank/#N/A and deletes it if yes
    If cel.Value = "" Or cel.Value = "#N/A" Then
        cel.EntireRow.Delete
    End If
Next cel

End Sub

This worked flawlessly on my sample data. Please bear in mind that headers are in the very first row in my sample data - .Range("C1:C" & lastRow)

I hope this solves your problem.

Problem with filters and entire row delete

1 Answers