G
Guest
The following code is supposed to compare all the names in row 1 with a list
on another worksheet. If the name is in the list it continues on, if the
name is not in the list it deletes the column, if the cell is empty (NULL)
then I want it to ignore it and go to the next cell.
As the code is now it deletes a couple of NULL cell columns at the begnning
of the worksheet, doesn't delete a few more, then deletes a couple of names
(properly as they are not on the list), then after it doesn't delete a column
(properly again because the name is on the list) it just deletes all the NULL
columns throughtout the rest of the row and doesn't delete anymore names.
The Null columns between names is to make the spreadsheet much easier to
view, so I want to keep them, I just want the program to ignore them and move
on.
Dim iRow As String
Dim cell As Range
Sub OfficeNameDelete()
With Sheets("Office")
For Each cell In .Range("$1:$1")
iRow = 0
On Error Resume Next
iRow = Application.VLookup(cell, Sheets("List").Range("$A:$B"), 1)
On Error GoTo 0
If cell = "TOTAL" Then
End
ElseIf cell = Null Then
Resume Next
ElseIf iRow <> cell Then
cell.EntireColumn.Delete
End If
Next cell
End With
End Sub
Thanks in advance. I am off to buy a VBA for dummies book now.
on another worksheet. If the name is in the list it continues on, if the
name is not in the list it deletes the column, if the cell is empty (NULL)
then I want it to ignore it and go to the next cell.
As the code is now it deletes a couple of NULL cell columns at the begnning
of the worksheet, doesn't delete a few more, then deletes a couple of names
(properly as they are not on the list), then after it doesn't delete a column
(properly again because the name is on the list) it just deletes all the NULL
columns throughtout the rest of the row and doesn't delete anymore names.
The Null columns between names is to make the spreadsheet much easier to
view, so I want to keep them, I just want the program to ignore them and move
on.
Dim iRow As String
Dim cell As Range
Sub OfficeNameDelete()
With Sheets("Office")
For Each cell In .Range("$1:$1")
iRow = 0
On Error Resume Next
iRow = Application.VLookup(cell, Sheets("List").Range("$A:$B"), 1)
On Error GoTo 0
If cell = "TOTAL" Then
End
ElseIf cell = Null Then
Resume Next
ElseIf iRow <> cell Then
cell.EntireColumn.Delete
End If
Next cell
End With
End Sub
Thanks in advance. I am off to buy a VBA for dummies book now.