Excel vba - Last row not changing

[quikdraw]

So, I am writing an app that will automatically generate reports that are done manually right now.

I am sorting an imported table, deleting rows that are not associated with a given department.

Later I am doing some cleanup and adding borders and such.. this is my code to sort the table:

With wsCpy3
.Range("A4:L" & CpyLstRw3).Copy _
wsDst3.Range("A" & DstLstRw1)
End With

Application.ScreenUpdating = False

wsDst3.Select

Dim lastRow As Long
Dim X As Long
Dim cRange As Range
Dim i As Long

lastRow = wsDst3.Cells(wsDst3.Rows.Count, "C").End(xlUp).Row

' Trim the values in column C
Set cRange = wsDst3.Range("C2:C" & lastRow)
For Each Cell In cRange
Cell.Value = WorksheetFunction.Trim(Cell.Value)
Next Cell

' Delete rows where column C is not "MS"
For i = lastRow To 2 Step -1 ' Loop backwards to avoid skipping rows after deletion
If wsDst3.Cells(i, "C").Value <> "MS" Then
wsDst3.Rows(i).Delete
End If
Next i

Application.ScreenUpdating = True

Later where I am formatting the table, when I again use the last row variable. It is still doing all rows down to the original row count, even if I declare the last row statement right before the table formatting.

How do I change that to format the table to the new last row after unnecessary rows have been deleted?

 

[Joe4]

It looks like you are deleting rows AFTER your set your lastRow variable.
You would then need to re-calculate lastRow AFTER you have done the row deletion, otherwise you have the value BEFORE the row deletion, not after.

 

[MARK858]

even if I declare the last row statement right before the table formatting.

Can you post the code where you "declare" the last row statement before the table formatting please, to us declaring the last row would just be a "Dim" line which wouldn't reset the last row (it just tells Excel what data type it should be [and if that is what you have then really you should get a duplicate declaration in current scope error if it is in the same routine]).

We could really do with seeing the full formatting code (preferably in code tags to make it easier to read and copy [paste the code in the thread, select the code and click the

icon at the top of the reply window, then post])

 

[quikdraw]

I will
I'll have to wait until in the office on Monday. Thank you for the pointers