Categorised Menu List

getLastRow, LastCellAddres
Getting last Active Row and Last active Cell Address in Ms. Excel

The following code can be placed in any Excel file Code Window (to get Code window, Press Alt + F11) if the Code window is password protected, then it will display a Dialog box to enter password, else, it takes you to code window.

gallery/get_last_row

Pre-requisite:

  • Place some data at any part of a sheet e.g., Column A contains 10 rows of data
  • Name the current sheet to as "Dump"
  • Declare variables lngDmpLastrow and strDmpLastCell 
  • let their datatype be LONG and STRING respectively,because ActiveCell.

Follow the following Procedure:

  • To get code window, press Alt + F11
  • Copy and paste the following code in code window
  • Place the cursor at Private sub and press F9, (to place a break point)
  • Place the cursor in any of the lines (click) between Private Sub and End Sub
  • Now Press F5 to compile and run, the you observe an yellow strip at Private Sub line
  • Now constantly but slowly press F8 key. Observe Yellow bar moving to each of the lines
  • When yellow line reaches End Sub, goto debug window (Press Ctrl + G) and type
  • ? lngDmpLastrow - Current row number is displayed
  • ? strDmpLastCell - Current Cell Address is displayed
gallery/getlastrow_samplecode

Sample Code

The above given code works only when the given sheet name is "Dump", but in real-time senario, you may have to get LastRowNumber or LastCellAddress of many sheets


Try using sheet name as Variable, so that it may work in any given sheet.

gallery/always end in sight

Row returns LONG value e.g., 104578 (to acccomodate Excel 2007 rows) and ActiveCell.Address returns STRING value e.g., $K$125