Excel

Last Dynamic Filled Row

Ease of Use

Easy

Version tested with

2000, 2002 

Submitted by:

Justinlabenne

Description:

Determines the range of cells that comprises the last filled row across as many columns 

Discussion:

If you have a varying range of data that you need to work with and it is always appearing in the last row, and it is unknown how many columns across it may take up, this option offers a method to determine the range. 

Code:

instructions for use

			

Option Explicit Sub LastRow_AcrossCols() ' Example used to select the last ' filled row across as many filled columns ' Find the last filled row in column A Dim lRow As Long lRow = Cells(Rows.Count, 1).End(xlUp).Row ' Find the last filled column in the last filled row Dim lCol As Long lCol = Cells(lRow, Columns.Count).End(xlToLeft).Column ' Select the range: Last row - Last Column Range("A" & lRow & ":" & Cells(lRow, lCol).Address).Select ' For example, just return the found range via message box MsgBox "Range is: " & _ Replace(Selection.Address, "$", Empty) End Sub

How to use:

  1. Open an Excel Workbook
  2. Copy the code
  3. Press Alt + F11 to open the Visual Basic Editor (VBE)
  4. Select INSERT > MODULE from the menubar
  5. Paste code into the right pane
  6. Press Alt+Q to return to Excel
  7. Save workbook before any other changes
 

Test the code:

  1. Add some data to the workbook of varying columns across and down rows.
  2. Go to TOOLS > MACRO > MACROS
  3. When the dialog appears, select (LastRow_AcrossCols)
  4. Press Run
  5. The code will select the last row across as many columns as are filled. Change the data and run the code again to see how it dynamically changes.
 

Sample File:

LastDynaRow.zip 8.32KB 

Approved by mdmackillop


This entry has been viewed 247 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express