ExcelBanter - Macro - Remove #n/a

I believe this code will work for you. Don't be confused by the fact that I
have a column defined to do some testing on - that's necessary. In the end,
entire rows with #N/A in that column (you may redefine it as needed in your
workbook) will be absolutely deleted - not just cleared out, but physically
deleted.

Since this is a destructive action, I suggest you first make a backup copy
of your workbook and put the code into either the backup or original and try
it to make sure it's doing what you want. That way, if something goes wrong
you'll still have an original copy of the workbook to fall back to.

To put this code into your workbook; open the workbook, press [Alt]+[F11] to
open the VB Editor, choose Insert | Module from the VBE menu and then copy
and paste the code into that module. Edit the column letter assigned to
constant testColumn as needed for your workbook. To use it, select the sheet
to be cleaned up, then use Tools | Macro | Macros and choose this one from
the list and click [Run].

Sub DeleteNARows()
'pick a column to test in, this
'column should be one that will have
'#N/A error displayed in it and that
'goes as far down the sheet as you need
'to examine for the #N/A conditions
'although not all entries have to be #N/A
'just something in them to the end
' Using column E for this example
' as E was where I put a VLOOKUP() formula
' to test/generate #N/A errors.
Const testColumn = "E" ' change as required
'no other changes to make
Dim naRowList As String
Dim anyRange As Range
Dim anyCellEntry As Range

'find area to examine:
Set anyRange = ActiveSheet.Range(testColumn & "1:" & _
ActiveSheet.Range(testColumn & _
Rows.Count).End(xlUp).Address)
'make list of rows that are to be deleted
For Each anyCellEntry In anyRange
If anyCellEntry.Text = "#N/A" Then
naRowList = naRowList & anyCellEntry.Row & _
":" & anyCellEntry.Row & ","
End If
Next
'remove the extra comma at the end of the list of rows
'that are to be deleted (if list is empty, exit sub)
If naRowList < "" Then
naRowList = Left(naRowList, Len(naRowList) - 1)
Else
'no #N/A entries found
Set anyRange = Nothing
Exit Sub
End If
Set anyRange = ActiveSheet.Range(naRowList)
anyRange.Delete Shift:=xlUp
Set anyRange = Nothing
End Sub

"Schwimms" wrote:

I have a macro that places #n/a's in rows, the next step for it to do is
delete them. I would like it to delete out the WHOLE rows that get populated
#n/a, not columns.