vba - need simple function to delete ranges
 

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

vba - need simple function to delete ranges
 

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

tom g <tom wrote in message
...
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

vba - need simple function to delete ranges
 

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)


"R.VENKATARAMAN" &&& wrote in message
...
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

tom g <tom wrote in message
...
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

vba - need simple function to delete ranges
 

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

"tom g" wrote:

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

vba - need simple function to delete ranges
 

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)


"tom g" <tom wrote in message
...
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

"tom g" wrote:

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