VBA Express : Excel - Working with the last data values in Rows or Columns

Excel

Working with the last data values in Rows or Columns

Ease of Use

Easy

Version tested with

2003

Submitted by:

mdmackillop

Description:

These simple User Defined Functions (UDF) return the last or preceding values in a row or column.

Discussion:

If you need to display differences or manipulate data from the last row(s) or column(s) of a set of data, these functions allow you to create formulae which will update whenever new data is added. Adding an offset variable to the function, will return the corresponding data, offset from the last. eg. =LR(2, -2) will give a value from 2 rows above the last.

Code:

instructions for use

			Option Explicit 
 
 'Last Row
Function LR(Col As Variant, Optional OS As Long) 
    Application.Volatile 
    If Not IsNumeric(Col) Then Col = Columns(Col).Column() 
    LR = Cells(Cells.Rows.Count, Col).End(xlUp).Offset(OS) 
End Function 
 
 'Last Column
Function LC(Rw As Variant, Optional OS As Long) 
    Application.Volatile 
    If Not IsNumeric(Rw) Then Rw = Rows(Rw).Row() 
    LC = Cells(Rw, Cells.Columns.Count).End(xlToLeft).Offset(, OS) 
End Function

How to use:

Open Microsoft Excel
Copy the Functions
Press Alt + F11 to open the Visual Basic Editor (VBE)
Add a new standard module (Top Left)
Paste code into the right pane
Save and exit the VBE

Test the code:

Return to excel and enter some data in cells A1:D5
Enter =LR(3) to return the last data in Column C
Enter =LR(3,-1) to return the previous value
Enter =LC(3) to return the last data in Row 3
Enter =LC(3,-1) to return previous value

Sample File:

LastValues.zip 9.47KB

Approved by mdmackillop

This entry has been viewed 312 times.