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.........
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.........