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
Loop
'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
Worksheets(sheetName).Rows(counter).Delete
End If
Next counter
'return control to the user
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub