PDA

View Full Version : delete all #n/a



Pete
12-03-2009, 09:13 AM
Hi

Need a macro to delete all the #n/a from column I - this is a tricky one.... i have managed to get this far..

not working...........

Sub Edit_3()
Application.ScreenUpdating = False
With Range("i6:i1000000")
On Error Resume Next
.SpecialCells(2, 16).ClearContents
.SpecialCells(-4123, 16).ClearContents
End With
Application.ScreenUpdating = True

End Sub


even tried recording the macro...

Sub Edit_3a()
Application.ScreenUpdating = False

ActiveSheet.Range("$A$5:$Z$1000000").AutoFilter Field:=9, Criteria1:="#N/A"
Range("I63231").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("I63231").Select
ActiveSheet.Range("$A$5:$Z$1000000").AutoFilter Field:=9
Range("I5").Select
Application.ScreenUpdating = True
End Sub

the problem lie with the fact the #n/a can start anywhere in column I from I6 onwards.........

Bob Phillips
12-03-2009, 09:40 AM
They should both work, so what are you getting?

mdmackillop
12-03-2009, 10:12 AM
I couldn't do without this one.

Sub DelErrors()
On Error Resume Next
If MsgBox("Clear error cells?", vbYesNo) = vbYes Then
Selection.SpecialCells(xlCellTypeFormulas, 16).ClearContents
Selection.SpecialCells(xlCellTypeConstants, 16).ClearContents
End If
End Sub

Bob Phillips
12-03-2009, 10:28 AM
That is essentially what his first macro (seems to) does MD.

mdmackillop
12-03-2009, 10:38 AM
So it is!

BTW Pete, How about the VBA formatting? Is it too much trouble?