Updating total cells after column deletions.
 

I have a worksheet that has a header column. Underneath it has two info
columns. There is a column between Hours and the date, there is another
column between names. I copy this sheet from another sheet which has
everyone's name then delete certain names based on office. I also delete the
hours columns on the names I keep as I do not need them and would have to
re-write many other modules which were based on the sheet not having that
column.

Someones Name Total
Hours Nov 1 - 15, 07 Hours Nov 1-15,07

86.67 2,500.00 86.67 2,500.00

I look at the name, if it is on the list I delete the Hours and (hours +1)
columns moving everything to the left, then I delete the two blank cells by
the name to line everything up again. The total figure adjusts correctly.
If the name is not on the list I delete the name column and the next 3
columns. When I do this the Total column does not update correctly and I get
a #ref in the cell. Here is the code:

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
.Columns(i + 1).Delete
.Columns(i).Delete
ElseIf Application.CountIf(Workbooks("Employee List for Payroll1") _
.Worksheets("List").Columns(2), .Cells(1, i)) 0 Then
Range(Cells(2, i), Cells(65536, i + 1)).Select
Selection.Delete xlShiftToLeft
Range(Cells(1, i + 1), Cells(1, i + 2)).Select
Selection.Delete xlShiftToLeft
End If
End If
Next
End With

What can I do so the Total column continues to adjust itself? As that
column is what I base several other worksheets on.

Thanks,

Kevin Porter

Updating total cells after column deletions.
 

Can you post sample of data that is on Shreveport and List worksheets along
with the column the data is located along with any formulas that are in the
cell. I can't tell from the posting where the #ref is occuring.

"Kevin Porter" wrote:

I have a worksheet that has a header column. Underneath it has two info
columns. There is a column between Hours and the date, there is another
column between names. I copy this sheet from another sheet which has
everyone's name then delete certain names based on office. I also delete the
hours columns on the names I keep as I do not need them and would have to
re-write many other modules which were based on the sheet not having that
column.

Someones Name Total
Hours Nov 1 - 15, 07 Hours Nov 1-15,07

86.67 2,500.00 86.67 2,500.00

I look at the name, if it is on the list I delete the Hours and (hours +1)
columns moving everything to the left, then I delete the two blank cells by
the name to line everything up again. The total figure adjusts correctly.
If the name is not on the list I delete the name column and the next 3
columns. When I do this the Total column does not update correctly and I get
a #ref in the cell. Here is the code:

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
.Columns(i + 1).Delete
.Columns(i).Delete
ElseIf Application.CountIf(Workbooks("Employee List for Payroll1") _
.Worksheets("List").Columns(2), .Cells(1, i)) 0 Then
Range(Cells(2, i), Cells(65536, i + 1)).Select
Selection.Delete xlShiftToLeft
Range(Cells(1, i + 1), Cells(1, i + 2)).Select
Selection.Delete xlShiftToLeft
End If
End If
Next
End With

What can I do so the Total column continues to adjust itself? As that
column is what I base several other worksheets on.

Thanks,

Kevin Porter