vba - need simple function to delete ranges

[Guest]

hi,
i'm very new to vba for excel.. working with very easy examples. wondering
if anyone can help me with a repetitive task.
i have data comming in to various columns. at some data there is a #NA, i'd
like to delete everything in that column after that #NA, and then move on to
the next column and repeat the task.

thanks, thomas

 

[R.VENKATARAMAN]

this is not an elegant solution for which you have to wait solution from
MVPs
meanwhile copy your data in another file and test this code and see whether
this is what ou want.

Public Sub test()
On Error GoTo line1
Dim cell As Range
For Each cell In Range("a1:F1")
cell.Activate
Cells.Find(what:="#n/a").Activate
ActiveCell.EntireColumn.Delete
Next cell
line1:
End Sub

 

[Bob Phillips]

Thomas,

Try this

Public Sub test()
Dim cell As Range
Dim crows As Long

For Each cell In ActiveSheet.UsedRange
If WorksheetFunction.IsError(cell.Value) Then
crows = Cells(Rows.Count, cell.Column).End(xlUp).Row
Range(Cells(cell.Row, cell.Column), Cells(crows,
cell.Column)).ClearContents
End If
Next cell
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

thanks guys that helps.. the only problem is that its not a function error.
its hard typed #NA ... it could easily be the word 'WRONG' as well.
how would i define that.
thanks, thomas

 

[Bob Phillips]

Then just change mine to

Public Sub test()
Dim cell As Range
Dim crows As Long

For Each cell In ActiveSheet.UsedRange
If LCase(cell.Value) = "#n/a" Then
crows = Cells(Rows.Count, cell.Column).End(xlUp).Row
Range(Cells(cell.Row, cell.Column), Cells(crows,
cell.Column)).ClearContents
End If
Next cell
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)