Check values in each column

[Martin]

Hi

I want to check the value of row 2 in each column and if it = "#N/A" then
delete the column. I have got this far but it doesn't wuite work. Can
anyone help?

For iColumn = 1 To 256
If Cells(2, iColumn).Value = "#N/A" Then
Columns(iColumn).EntireColumn.Delete
Next iColumn

Thanks in advance

 

[OssieMac]

Hi Martin,

Try one of the following

If WorksheetFunction.IsNA(Cells(2, iColumn).Value) Then

or

If WorksheetFunction.IsError(Cells(2, iColumn).Value) Then

Check Help under Is functions on the worksheet (Not in VBA editor) for more
info on these functions.

Don't know if you know this but you need to actually click on Help while in
the worksheet to see the help relating to the worksheet and the same if in
VBA you need to click on help while in the VBA editor otherwise you will not
be seeing the correct help. I included this because a lot of people have help
open and when they change between the worksheet and VBA they do not refresh
help and of course they cannot find what they are after.

 

[Martin]

THanks for the advice however th #N/A is the actual text and I'm not looking
for an error.

When I run the code below I get error code 13, type mismatch

 

[OssieMac]

Hi again Martin,

Use your method of comparison but with the following modification.

For iColumn = 256 to 1 Step -1

Reason is with your method if you delete a column when icolumn = 10 then
column 11 becomes the new column 10 and the next iteration of icolumn will be
11. Going backwards if you delete column 10 then the next iteration of
icolumn is 9 and that column has not been moved.

Even when I first posted I missed that because I was only looking at the test.

 

[Jacob Skaria]

Martin

--you can either you Worksheetfunction.ISNA() as suggested by OssieMac or
use .Text property of range to check NA() s as below.

--You can use the .End(xlToLeft) to determin the last column in that row
with any data.This way you can avoid unnecessary loops..

Dim lngCol As Long
For lngCol = Cells(2, Columns.Count).End(xlToLeft).Column To 1 Step -1
If Cells(2, lngCol).Text = "#N/A" Then Columns(lngCol).Delete
Next

If this post helps click Yes