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
If you use End(xlDown), it stops at the first empty cell in the middle – and many students' macros process only half the data. Always search from the bottom up (Rows.Count … End(xlUp)), and pick a column that has a value in every row (Order ID).
Ravindra Bagale's Tip – मराठी
End(xlDown) वापरला तर मधे एखादी रिकामी cell असेल तिथे तो थांबतो – आणि बऱ्याच students चा macro अर्धाच data process करतो. नेहमी खालून वरती (Rows.Count … End(xlUp)) शोधा, आणि असा column निवडा ज्यात प्रत्येक row ला value आहे (Order ID).
Ravindra Bagale's Tip – हिंदी
End(xlDown) इस्तेमाल किया तो बीच में कोई खाली cell हो तो वह वहीं रुक जाता है – और बहुत से students का macro आधा ही data process करता है. हमेशा नीचे से ऊपर (Rows.Count … End(xlUp)) ढूँढो, और ऐसा column चुनो जिसमें हर row में value हो (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.