Macro to delete rows that have #N/A in a cell

[Dazzawm]

I need a macro that will look at column G and any cell that has #N/A in it I want the entire row deleted. I need a macro because I want to add it on the end of an existing macro I have and sorting and deleting would not work because each file will have different amounts. Thanks.

 

[VoG]

Try

Sub Delete_Rows()
Dim LR As Long, i As Long
LR = Range("G" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If IsError(Range("G" & i)) Then Rows(i).Delete
Next i
End Sub

 

[Smitty]

Always try running code on a sample of your data, and then test it by itself. You should make sure it fits in your application before adding it to anything else.

 

[Dazzawm]

Thanks VoG and everyone else. Yours was the only one that seemed to work when added to the bottom of my code, although it took a while!

 

[VoG]

Turn off screen updating anfd automatic calculation. you should know how to do this by now.

If not post back.

Smitty's code should be faster whatever but you'll need an offset

 

[Dazzawm]

Turn off screen updating anfd automatic calculation. you should know how to do this by now.

If not post back.

Smitty's code should be faster whatever but you'll need an offset

I have done that but is no quicker, but it doesn't matter.

 

[Dazzawm]

I need another macro but recording it wont work as I have a different amount of rows in each file I will use it on. Starting in A2 I need it to number down 1 to whatever row is the last i.e 1 to 7386 rows when there is that amount. Its another part I can add to my macro. Thanks.

 

[Dazzawm]

I need another macro but recording it wont work as I have a different amount of rows in each file I will use it on. Starting in A2 I need it to number down 1 to whatever row is the last i.e 1 to 7386 rows when there is that amount. Its another part I can add to my macro. Thanks.

Hi VoG, forget about this request as I got it sorted but going back to my original question it is still taking quite a long time even after doing as you said and it would be quicker sorting deleting manually which defeats the purpose somewhat. Is there no other way to make it quicker please?

 

[VoG]

Try

Sub Delete_Rows()
Dim LR As Long
LR = Range("G" & Rows.Count).End(xlUp).Row
On Error Resume Next
Range("G2:G" & LR).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
End Sub