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.

This should be close. Traversing a range of cells and deleteing rows within
that range is problematic. You are tring to move through a range that keeps
on changing. The code that I am posting basically creates a single range to
be deleted at the end.

Sub DeleteStuff()
Dim rng As Range
Dim rngDelete As Range

Set rng = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(-1, 0)
Do While rng.Row 1
If Application.CountIf(Sheets("Sheet2").Columns(1), rng.Value) = 0
Then
If rngDelete Is Nothing Then
Set rngDelete = rng
Else
Set rngDelete = Union(rng, rngDelete)
End If
End If
Set rng = rng.Offset(-1, 0)
Loop
If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete
End Sub

--
HTH...

Jim Thomlinson


"Kevin Porter" wrote:

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.

Ok. That makes a lot of sense, with the range continuously changing. It
makes my result make sense.

The code you gave me goes down column A on the first worksheet, when I need
it to go across row 1. Then compare it to values in the second worksheet in
Column A.

I have played with it a little bit, but it is still doing the same thing. I
am looking it up in the book I bought at lunch, but any help would be
appreciated.

Thanks again for taking the time to help a novice.

"Jim Thomlinson" wrote:

This should be close. Traversing a range of cells and deleteing rows within
that range is problematic. You are tring to move through a range that keeps
on changing. The code that I am posting basically creates a single range to
be deleted at the end.

Sub DeleteStuff()
Dim rng As Range
Dim rngDelete As Range

Set rng = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(-1, 0)
Do While rng.Row 1
If Application.CountIf(Sheets("Sheet2").Columns(1), rng.Value) = 0
Then
If rngDelete Is Nothing Then
Set rngDelete = rng
Else
Set rngDelete = Union(rng, rngDelete)
End If
End If
Set rng = rng.Offset(-1, 0)
Loop
If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete
End Sub

--
HTH...

Jim Thomlinson


"Kevin Porter" wrote:

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.

Sub OfficeNameDelete()
Dim i As Long
Dim lastCol as Long
With Sheets("Office")
lastcol = .cells(1,"IV").End(xltoLeft).column
For i = lastcol to 1 step -1
if len(trim(.cells(1,i))) < 0 then
if
application.Countif(worksheets("List").Columns(1), .cells(1,i)) = 0 then
.columns(i).Delete
end if
end if
Next
end With
End Sub

--
Regards,
Tom Ogilvy


"Kevin Porter" wrote:

Ok. That makes a lot of sense, with the range continuously changing. It
makes my result make sense.

The code you gave me goes down column A on the first worksheet, when I need
it to go across row 1. Then compare it to values in the second worksheet in
Column A.

I have played with it a little bit, but it is still doing the same thing. I
am looking it up in the book I bought at lunch, but any help would be
appreciated.

Thanks again for taking the time to help a novice.

"Jim Thomlinson" wrote:

This should be close. Traversing a range of cells and deleteing rows within
that range is problematic. You are tring to move through a range that keeps
on changing. The code that I am posting basically creates a single range to
be deleted at the end.

Sub DeleteStuff()
Dim rng As Range
Dim rngDelete As Range

Set rng = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(-1, 0)
Do While rng.Row 1
If Application.CountIf(Sheets("Sheet2").Columns(1), rng.Value) = 0
Then
If rngDelete Is Nothing Then
Set rngDelete = rng
Else
Set rngDelete = Union(rng, rngDelete)
End If
End If
Set rng = rng.Offset(-1, 0)
Loop
If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete
End Sub

--
HTH...

Jim Thomlinson


"Kevin Porter" wrote:

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.

Thanks, that works GREAT!!!!

Just one extra thing. Is there a way that we can delete the column set for
deletion and the next column? The reason being is when there are several
deleted names in a row it leaves the empty columns, which stack up, so
sometimes there is one empty column and sometimes there are more. it isn't
that bad on this sheet, but when I do similar code for other sheets there
will be more deletion than not.

Thanks again.

"Tom Ogilvy" wrote:


Sub OfficeNameDelete()
Dim i As Long
Dim lastCol as Long
With Sheets("Office")
lastcol = .cells(1,"IV").End(xltoLeft).column
For i = lastcol to 1 step -1
if len(trim(.cells(1,i))) < 0 then
if
application.Countif(worksheets("List").Columns(1), .cells(1,i)) = 0 then
.columns(i).Delete
end if
end if
Next
end With
End Sub

--
Regards,
Tom Ogilvy


"Kevin Porter" wrote:

Ok. That makes a lot of sense, with the range continuously changing. It
makes my result make sense.

The code you gave me goes down column A on the first worksheet, when I need
it to go across row 1. Then compare it to values in the second worksheet in
Column A.

I have played with it a little bit, but it is still doing the same thing. I
am looking it up in the book I bought at lunch, but any help would be
appreciated.

Thanks again for taking the time to help a novice.

"Jim Thomlinson" wrote:

This should be close. Traversing a range of cells and deleteing rows within
that range is problematic. You are tring to move through a range that keeps
on changing. The code that I am posting basically creates a single range to
be deleted at the end.

Sub DeleteStuff()
Dim rng As Range
Dim rngDelete As Range

Set rng = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(-1, 0)
Do While rng.Row 1
If Application.CountIf(Sheets("Sheet2").Columns(1), rng.Value) = 0
Then
If rngDelete Is Nothing Then
Set rngDelete = rng
Else
Set rngDelete = Union(rng, rngDelete)
End If
End If
Set rng = rng.Offset(-1, 0)
Loop
If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete
End Sub

--
HTH...

Jim Thomlinson


"Kevin Porter" wrote:

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.

Hey I figured this one out on my own. Maybe I am learning something.

I added

..Columns(i + 1).Delete

after the original delete command and it works.

Thanks again for your help.

"Kevin Porter" wrote:

Thanks, that works GREAT!!!!

Just one extra thing. Is there a way that we can delete the column set for
deletion and the next column? The reason being is when there are several
deleted names in a row it leaves the empty columns, which stack up, so
sometimes there is one empty column and sometimes there are more. it isn't
that bad on this sheet, but when I do similar code for other sheets there
will be more deletion than not.

Thanks again.

"Tom Ogilvy" wrote:


Sub OfficeNameDelete()
Dim i As Long
Dim lastCol as Long
With Sheets("Office")
lastcol = .cells(1,"IV").End(xltoLeft).column
For i = lastcol to 1 step -1
if len(trim(.cells(1,i))) < 0 then
if
application.Countif(worksheets("List").Columns(1), .cells(1,i)) = 0 then
.columns(i).Delete
end if
end if
Next
end With
End Sub

--
Regards,
Tom Ogilvy


"Kevin Porter" wrote:

Ok. That makes a lot of sense, with the range continuously changing. It
makes my result make sense.

The code you gave me goes down column A on the first worksheet, when I need
it to go across row 1. Then compare it to values in the second worksheet in
Column A.

I have played with it a little bit, but it is still doing the same thing. I
am looking it up in the book I bought at lunch, but any help would be
appreciated.

Thanks again for taking the time to help a novice.

"Jim Thomlinson" wrote:

This should be close. Traversing a range of cells and deleteing rows within
that range is problematic. You are tring to move through a range that keeps
on changing. The code that I am posting basically creates a single range to
be deleted at the end.

Sub DeleteStuff()
Dim rng As Range
Dim rngDelete As Range

Set rng = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(-1, 0)
Do While rng.Row 1
If Application.CountIf(Sheets("Sheet2").Columns(1), rng.Value) = 0
Then
If rngDelete Is Nothing Then
Set rngDelete = rng
Else
Set rngDelete = Union(rng, rngDelete)
End If
End If
Set rng = rng.Offset(-1, 0)
Loop
If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete
End Sub

--
HTH...

Jim Thomlinson


"Kevin Porter" wrote:

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.