I'm trying to delete columns based on test in row1 of the worksheet. Here's an example of what I'm using.
I want to delete all columns that contain 'no'. There are 169 columns in this worksheet, with 130 of those with 'no'.
Here's the code that I've written to delete the columns. The code deletes only some of the columns with a no--only the 1st column of adjacent no will be deleted. For ex: Col 9, 10 and 11 have 'no'. Only 9 will delete but not Col 10 and 11.
Dim cell As Range
Sheet1.Activate
For Each cell In Sheet1.Range("A1:FM1").Cells
If cell.Value = "no" Then
cell.EntireColumn.Select
Selection.Delete
End If
Next cell
End Sub
What could be going wrong.
I want to delete all columns that contain 'no'. There are 169 columns in this worksheet, with 130 of those with 'no'.
no | Y | no | Y | Y | Y | Y | Y | no | no | no | Y | Y | no | Y | no | no | Y | Y | Y | Y | Y | Y | no | no |
Col 1 | Col 2 | Col 3 | Col 4 | Col 5 | Col 6 | Col 7 | Col 8 | Col 9 | Col 10 | Col 11 | Col 12 | Col 13 | Col 14 | Col 15 | Col 16 | Col 17 | Col 18 | Col 19 | Col 20 | Col 21 | Col 22 | Col 23 | Col 24 | Col 25 |
Here's the code that I've written to delete the columns. The code deletes only some of the columns with a no--only the 1st column of adjacent no will be deleted. For ex: Col 9, 10 and 11 have 'no'. Only 9 will delete but not Col 10 and 11.
Dim cell As Range
Sheet1.Activate
For Each cell In Sheet1.Range("A1:FM1").Cells
If cell.Value = "no" Then
cell.EntireColumn.Select
Selection.Delete
End If
Next cell
End Sub
What could be going wrong.