Removing rows in Excel

Today I had to remove all rows in an excel sheet containing #N/A in the third cell. The only catch is that there are thousands and thousands of these rows and I need to be able to repeat this action.

Sub RemoveBadOnes()
    'speed things up
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim sheetName As String
    sheetName = "copy of clean"
    'get number of non-empty rows
    '(probably not the most optimal way to do this)
    Dim numRows As Integer
    numRows = 1
    Do While Not IsEmpty(Worksheets(sheetName).Cells(numRows, 1).Value)
        numRows = numRows + 1
    'go trough rows starting with the last one
    Dim counter As Integer
    For counter = numRows To 1 Step -1
        'remove each row containing #N/A in third cell
        If IsError(Worksheets(sheetName).Cells(counter, 3).Value) Then
        End If
    Next counter
    'return control to the user
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Comments are closed.