Updating total cells after column deletions.

[Kevin Porter]

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

 

[Joel]

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.