1

All this macro does is loop through column A, and if it hits an empty value it deletes the entire row. I tried limiting it to a set range to speed it up, but am out of ideas.

I've turned screen updating off earlier in the process.

Thanks.

Sub DeleteErrorRows()
    
    Dim rng As Range
    Dim i As Long
    Set rng = ThisWorkbook.ActiveSheet.Range("A1:A10000")
    With rng
        ' Loop through all cells of the range
        ' Loop backwards, hence the "Step -1"
        For i = .rows.Count To 1 Step -1
            If .Item(i) = "" Then
                ' Since cell is empty, delete the whole row
                .Item(i).EntireRow.Delete
            End If
        Next i
    End With

End Sub
  • make sure Application.Calculation = xlCalculationManual, and Application.EnableEvents = False , so it doesnt recalc or raise events every time a row gets deleted. – Joe Bourne Jul 06 '22 at 20:41
  • If rows wanted is less than rows to delete then copy? – Solar Mike Jul 06 '22 at 21:08
  • If `.Range("A1").CurrentRegion` references your (table) range and the first row has headers, you could use [this code](https://stackoverflow.com/a/71967085). Just replace `O1` with `A1` and `"Resigned"` with `""` and reference the correct worksheet. – VBasic2008 Jul 06 '22 at 21:21

1 Answers1

1

Using SpecialCells

Sub DeleteErrorRows()
    On Error Resume Next 'ignore error if no blanks found
    ThisWorkbook.ActiveSheet.Range("A1:A10000"). _
                SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125