Menu
How can I find the last row that contains data in a specific column and on a specific sheet?
623
Locate the last cell that contains data or formatting on a worksheet. To locate the last cell that contains data or formatting, click anywhere in the worksheet, and then press CTRL+END.
Ctrl-Down -- The shortcut moves the cursor to the last row with data before the first blank row that is encountered; this may be the last row in the table ideally, but only if there are not any blank rows in the table.
We use Range. SpecialCells() method in the below VBA Code to find and return details of last used row, column, and cell in a worksheet. Concatenate all three variables (LastRow/LastCol/LastCell), add a new line between variables use Chr(10). Show the final output in an Excel Message box.
Another method to use excel formulas to find the last row number with data is to use a combination of ROW, INDEX, and ROWS functions. In Microsoft Excel, the INDEX function returns the value at a certain position in a range or array. We will find the number of the last row from the following dataset.
How about:
Function GetLastRow(strSheet, strColumn) As Long Dim MyRange As Range Set MyRange = Worksheets(strSheet).Range(strColumn & "1") GetLastRow = Cells(Rows.Count, MyRange.Column).End(xlUp).Row End Function Regarding a comment, this will return the row number of the last cell even when only a single cell in the last row has data:
Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row You should use the .End(xlup) but instead of using 65536 you might want to use:
sheetvar.Rows.Count That way it works for Excel 2007 which I believe has more than 65536 rows
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
