Excel VBA Autofilter > Delete Empty Rows

Question

We have a sheet that we use to analyse a detailed tendering process, and want to delete any empty rows.

The range can vary per project, up to maybe 170 columns and 6000 rows.

The code I have tested is working, on a project with approx. 40 columns & 4750 rows, and is taking just shy of 10 mins to run.

Looking for any slightly more elegant solutions to bring this time down. Currently the code will Autofilter every column for blanks, wondering if even empty columns being filtered is slowing the whole thing down?

In below code i removed most of the autofilter fields for viewing ease, but it filters every field from 1-175.

Sub DeleteEmptyRows()

With Sheets("Detailed Comparison")
    Application.DisplayAlerts = False
    .AutoFilterMode = False
    Application.ScreenUpdating = False

    With .Range("F24:FY6000")
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="="
        .AutoFilter Field:=2, Criteria1:="="
        .AutoFilter Field:=175, Criteria1:="="
    End With

    With .Range("F25:FY6000").SpecialCells(xlCellTypeVisible).Rows.Delete
    End With

    Application.DisplayAlerts = True

    .AutoFilterMode = False
    Application.ScreenUpdating = True
End With

End Sub

Answer 1

To make things more elegant

1. Add a column that evaluates to TRUE when cells in columns 1-175 are blank. Filter on this column.

2. To better define the rows you need to delete, use a function to define the bottom row (rather than setting bottom row to 6000.

e.g.:

Function LastRowInOneColumn(ws As Worksheet, Optional bool As Boolean) As Long

'Find the last used row in a Column
'by default, returns row of column A (FLASE)
'if bool is TRUE then will return row of column B

Dim LastRow As Long
Dim col As String

If bool = True Then
    col = "B"
Else
    col = "A"
End If

With ws
    LastRow = .Cells(.Rows.Count, col).End(xlUp).row
End With

LastRowInOneColumn = LastRow

End Function

Speed

I recommend that you test to see what part of your code is running so slowly. If it is the filtering, than suggestion 1 (above) should help. If it is the deleting than it may be that other parts of your workbook are linking to this data set and thus deleting data here will be VERY slow. If this is the case, my recommendation is to change your other data sets so that they refer to this worksheet via a named range that you delete as step one of your DeleteEmptyRows macros and then re-create these named ranges at the end of running the macro

Sub set_named_ranges()

'creates named ranges needed for this workbook
'this code is somewhat crude, you may need to modify based on how your data are laid out

Dim found As Range
Dim col_lookup_text As String
dim wks_name As String

wks_name = "Detailed Comparison"

Worksheets(wks_name).Select
Worksheets(wks_name).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select

'header named range
ActiveWorkbook.Names.Add _
        Name:=("data_Header"), _
        RefersTo:=Range(wks_name & "!" & RngAddress(Selection))

'main data named range
Range(Selection, Selection.End(xlDown)).Select

ActiveWorkbook.Names.Add _
        Name:=("dataset"), _
        RefersTo:=Range(wks_name & "!" & RngAddress(Selection))

End Sub

Function RngAddress(rng As Range) As String
RngAddress = rng.Address
End Function

and:

Sub delete_these_named_ranges(ParamArray names_of_named_ranges() As Variant)

'not a very sexy macro
'feed macro names of named ranges
'deletes the named range
'if named range doesn't exist, it creates a named range with
'that name and deletes it to avoid errors

Dim nName As Variant

For Each nName In names_of_named_ranges

    On Error Resume Next
    ActiveWorkbook.Names.Add Name:=nName, RefersTo:="temp"
    ActiveWorkbook.Names(nName).Delete

Next nName

End Sub

Answer 2

You could add an additional column with the count of all none empty fields of the row - e.g. =COUNTA(F24:FY24) - and then filter the rows on this column where the value = 0.

I haven't tested this, but as guess it should be way faster...