VBA Express : Excel - Last Dynamic Filled Row

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:

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

Test the code:

Add some data to the workbook of varying columns across and down rows.
Go to TOOLS > MACRO > MACROS
When the dialog appears, select (LastRow_AcrossCols)
Press Run
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 258 times.