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]

Are these cells containing formulas that return #N/A?

 

[Smitty]

You could record a macro using AutoFilter to trap the #N/A's then Goto-->Special-->Visible Cells Only-->Delete rows.

It'll be faster than trying to loop through.

HTH,

 

[Dazzawm]

Are these cells containing formulas that return #N/A?

No, they did have but I copied and paste specialed values earlier in my macro.

 

[Dazzawm]

You could record a macro using AutoFilter to trap the #N/A's then Goto-->Special-->Visible Cells Only-->Delete rows.

It'll be faster than trying to loop through.

HTH,

Gone a little over my head that one!!

 

[Nogslaw]

Whenever you are deleting rows, you always want to start at the bottom of your range. This can be rather slow though.

Sub Delete_Rows()
For r = 1000 to 1 Step -1  'THE ROWS THAT YOU WANT TO EXAMINE
    If Application.Worksheetfunction.Isna(Sheets("Sheet1").Range("G" & r).value) Then Sheets("Sheet1").Range("G" & r).EntireRow.Delete
Next r
End Sub

 

[Smitty]

Gone a little over my head that one!!

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    LR = Cells(Rows.Count, "G").End(xlUp).Row<br>    <br>    Range("G2").AutoFilter<br>    <br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.Range("$G$1:$G$" & LR)<br>            .AutoFilter Field:=1, Criteria1:="#N/A"<br>            .SpecialCells(xlCellTypeVisible).Select<br>            .Delete Shift:=xlUp<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[Dazzawm]

The row amounts may vary in each file, anything up to a possible 20,000 rows.

 

[Nogslaw]

As usual, Smitty's solution is very good.

 

[Dazzawm]

Sub Foo()
Dim LR As Long
LR = Cells(Rows.Count, "G").End(xlUp).Row

Range("G2").AutoFilter

With ActiveSheet.Range("$G$1:$G$" & LR)
.AutoFilter Field:=1, Criteria1:="#N/A"
.SpecialCells(xlCellTypeVisible).Select
.Delete Shift:=xlUp
End With

End Sub

HTH,

When added to the bottom of my macro it didn't get rid of them plus I lost my headers in row 1.