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" wrote:
> 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" wrote:
>
> > 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.
> >
> > "Kevin Porter" wrote:
> >
> > > 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.
|
Similar Threads
