Deleteing columns starting at a certain row.

[Kevin Porter]

I currently have a program that compares names in row 1 with another
spreadsheet and deletes the columns if the names aren't there. This was easy
as there was only 1 column under a name.

Now there is another column that I do not want at all. So I need it to
delete all the columns for names that are not there and the first two columns
for those that are.

Here is my current code:

With Sheets("Shreveport").Rows(2).Delete
Dim i As Long
Dim lastCol As Long
With Sheets("Shreveport")
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(Workbooks("Employee List for
Payroll1").Worksheets("List").Columns(2), .Cells(1, i)) = 0 Then
.Columns(i).Delete .Columns(i + 1).Delete
End If
End If
Next
End With

Any help would be appreciated.

Thanks.

 

[JLGWhiz]

The way that you set up you value for the i variable, it is equal to you last
column with data. That means that when you try to delete i+1 it is a blank
column.

The other problem is the syntax: .Columns(i).Delete .Columns(i + 1).Delete
won't work. You could do: .Columns(i & ":" & i - 1).Delete
which would delete the last two columns in the defined range for the first
iteration.
On every subsequent iteration where your If criteria is met it will delete
the column meeting the criteria and the column to the immediate left. I
don't think either case is what you really want.

 

[JLGWhiz]

Make that: .Columns(i, i - 1).Delete

Can't use the colon with numerical values.

 

[Kevin Porter]

Correct the column is blank when I delete i+1 which is exactly what I wanted
when I had a column to delete and the blank column next to it. Worked great.

Now however, if the name does not exist on my list it needs to delete 4 rows
(one with info, one blank, info blank. This I can do no problem by deleteing
i a couple more times.) and if the name is on my list I now need it to delete
columni.row2 and columni.row2+1, moving the other two rows to the left.

This is how the spread sheet look.

Row 1 has a name in column 1, then 3 blank columns, then another name, then
3 blanks and so.

Row two has a hours column, blank column, dollar column, blank column.

If the name in row one is not on my list I need to all 4 columns (the name
column and 3 blanks on row 1, the hours and dollar columns on row 2).

If the name is on my list, I need to keep the row 1 name column and 1 blank
column for spacing. I need to delete the hours column (starting on row 2 and
going down, and 1 blank column (row 2 and down) and move all the columns to
the left.

I hope this makes sense. Its kind of confusing and I am lokking at what i
am doing...lol.

Thanks.

 

[JLGWhiz]

Kevin, I am not sure that I completely understood your sheet layout, but you
can run this on a copy and let me know what works and what don't. Maybe I
can then modify it to meet your needs.

Sub delComb()
Dim i As Long
Dim lastCol As Long
With Sheets("Shreveport")
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(Workbooks("Employee List for Payroll1") _
..Worksheets("List").Columns(2), .Cells(1, i)) = 0 Then
.Range(Cells(1, i), Cells(1, i+3)).EntireColumn.Delete 'gets 4 columns
ElseIf Application.CountIf(Workbooks("Employee List for Payroll1") _
..Worksheets("List").Columns(2), .Cells(1, i)) > 0 Then
.Range(Cells(2, i+1), Cells(65536, i +2)).Delete xlShiftToLeft
End If
End If
Next
End With
End sub

 

[Kevin Porter]

JLG,

It worked perfect. Exactly as I was describing. Unfortunately, my logic
was faulty. I was starting at the far right and working my way backwards,
but this caused my headings on the columns to be off.

However, if i start at the left, I can delete the first two columns that
start with Hours (this moves my dollar columns under the names where I want
them), then just clear all the hours columns until there is no name in row 1.

I was better able to visualize what I actually needed to do whne I 'GASP"
did it manually. I think I can adjust your code to work for me.

I really appreciate your help. I learned a valuable lesson in figuring it
out manually what I need before trying to code it.

Thanks again,

I am sure I will be back at some point (maybe even on this same issue with
my new procedure).

Kevin