12.11 Finding the Last Row (and Column)
Data size changes every day, so never hard-code 11 or 5000.
Dim lastRow As Long, lastCol As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' last filled row in column A
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column ' last filled column in row 1
It works like pressing Ctrl + ↑ from the very bottom of column A. Alternatives: ws.Range("A1").CurrentRegion.Rows.Count; for an Excel Table ws.ListObjects("tblOrders").ListRows.Count.
Worked example. Range("A2:A" & lastRow) builds the address text "A2:A4801" when there are 4,800 orders.
Ravindra Bagale's Tip
End(xlDown) vaparla tar madhe ekhadi rikami cell asel tithe to thambto – aani khup students cha macro ardhach data process karto. Nehmi khalun varti (Rows.Count … End(xlUp)) shodha, aani asa column nivda jyat pratyek row la value aahe (Order ID).
Practice task
Write a macro that shows the last row, last column and the full data address (e.g. A1:I4801) of the Orders sheet in a MsgBox.