Deleting rows from a spreadsheet where Column C is blank

I have a script which I use to delete rows from a Excel Sheet, dependent on the Column C cell of that row being blank.

I believe deleting a range might be quicker than deleting individual rows, but I'm unsure.

Sub Shorter()

'47ms to remove blanks rows of 80 rows

'Searches the designated Column and checks for a Blank Cell
'It then Sets a Marker and then continues until it finds a cell with data in it.
'That blank range is then deleted.

    t = GetTickCount
    With ActiveSheet

        Firstrow = 20
        LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        For i = LastRow To Firstrow Step -1

            If i = StartRow - 1 Then GoTo EndSubAutoDeleteRowsFromEndRow
            If .Cells(i, "C") = Empty Then

                DelRangeStartRow = i

                ii = 0

                Do While .Cells(i - ii, "C") = Empty
                    DelRangeEndRow = (i - ii)
                    ii = ii + 1
                Loop

                Range(.Rows(DelRangeStartRow), .Rows(DelRangeEndRow)).Delete Shift:=xlUp

            End If
        Next i
    End With
    'Come here if you are finished deleting

    EndSubAutoDeleteRowsFromEndRow:

    SecondsElapsed = GetTickCount - t

End Sub

Things I like:

-
Going last row to first, so as to avoid messiness with row numbers that occur when deleting whole rows.

-
Good use of With

-
Good function naming of GetTickCount (at least, assuming it does what it says it does)

And now that that's done:

Option Explicit

This should be at the top of every code module you ever create in VBA. Go to Tools --> Options --> Require Variable Declaration and it will automatically insert it for you from now on.

This is important because without it, VBA will interpret any new variable names (including mis-spellings) as entirely new variables, instead of what you intended.

It also forces you to declare your variables. So you must explicitly give them a type (Long, String, Variant etc.) and a scope (Procedure Dim var As, Module Private Var As, Project Public Var As).

This will then automatically catch all sorts of unintended situations (such as accidentally setting a number equal to an object) which would not be caught if VBA has to assume that all your variables are Variant because you never explicitly declared them.

Naming

Good Variable naming is one of the most important skills you can develop as a developer. Variable names (including values, objects, functions, even whole projects) should be Clear, Concise and, above all, Unambiguous.

Your variable names here are good. Your function name is not. Longer bears absolutely no relation to "Deleting Rows".

A function name like RemoveRowsWithEmptyCellsInRange would be far better.

Re-usability

Rather than hard-coding this function for column C of the active sheet, why not create a function to do it for any column at all?

Perhaps something like this?

Private Sub DescriptiveNameHere()

    Dim columnIndex As Long
    columnIndex = 3 '/ Column "C"

    Dim firstRow As Long, lastRow As Long
    firstRow = 20
    lastRow = Cells(Rows.Count, columnIndex).End(xlUp).row

    RemoveRowsWithEmptyCellsInColumn ActiveSheet, columnIndex, firstRow, lastRow

End Sub

Public Sub RemoveRowsWithEmptyCellsInColumn(ByRef targetSheet as Worksheet, ByVal columnIndex As Long, ByVal firstRow As Long, ByVal lastRow As Long)

    Dim ws as Worksheet
    Set ws = targetSheet
    ws.Activate

    Dim row As Long, col As Long
    Dim checkCell As Range

    col = columnIndex
    For row = lastRow To firstRow Step -1
        Set checkCell = ws.Cells(row, col)
        If IsEmpty(checkCell) Then Rows(row).Delete
        '/ You might also want to check for cells that appear empty but contain E.G. a formula that is currently displaying no value, like so:
        ' If checkCell.Text = "" Then Rows(row).Delete
    Next row

End Sub

Speed

Very low-hanging standard VBA fruit here:

Public Sub DisableApplicationSettings()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

End Sub

Public Sub ResetApplicationSettings()

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Put that at the start and end of any VBA program and it will run inordinantly faster.

However, it doesn't take into account how the spreadsheet was before your program started (what if your user already had calculation set to manual?).

So, these, coupled with some public variables and some error handling so premature ending of the code execution still restores the settings, is even better:

Public VarScreenUpdating    As Boolean
Public VarEnableEvents      As Boolean
Public VarCalculation       As XlCalculation

Public Sub StoreApplicationSettings()

    VarScreenUpdating = Application.ScreenUpdating
    VarEnableEvents = Application.EnableEvents
    VarCalculation = Application.Calculation

End Sub

Public Sub DisableApplicationSettings()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

End Sub

Public Sub RestoreApplicationSettings()

    Application.ScreenUpdating = VarScreenUpdating
    Application.EnableEvents = VarEnableEvents
    Application.Calculation = VarCalculation

End Sub

StackExchange Code Review Q#116556, answer score: 9