Return last numeric value in a column
To return the last numeric value in a column we can apply two methods using an Excel VLOOKUP function or a combination of an Excel INDEX and MATCH functions
Example: Return last numeric value in a column
The formula uses the Excel INDEX and MATCH functions to return the last numeric value in column B.
|
METHOD 2. Return last numeric value in a column using the Excel VLOOKUP function
EXCEL
=VLOOKUP(9.99999999999999E+307,B:B,1)
|
The formula uses the Excel VLOOKUP function to return the last numeric value in column B.
|
Dim ws As Worksheet
ws.Range("E4").Value = Application.WorksheetFunction.Index(ws.Range("B:B"), Application.WorksheetFunction.Match(9.99999999999999E+307, ws.Range("B:B")))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E4") in the VBA code to any cell in the worksheet.
Column Reference: Select the column that you are searching through for the last numeric value by changing the column reference ("B:B") in the VBA code to any column in the worksheet, that doesn't conflict with the formula.
METHOD 2. Return last numeric value in a column using the Excel VLOOKUP function
VBA
Dim ws As Worksheet
ws.Range("E4").Value = Application.WorksheetFunction.VLookup(9.99999999999999E+307, ws.Range("B:B"), 1)
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E4") in the VBA code to any cell in the worksheet.
Column Reference: Select the column that you are searching through for the last numeric value by changing the column reference ("B:B") in the VBA code to any column in the worksheet, that doesn't conflict with the formula.
To return the last numeric value in a column we can apply two methods using an Excel VLOOKUP function or a combination of an Excel INDEX and MATCH functions.
=INDEX(column_ref,MATCH(9.99999999999999E+307,column_ref))
=VLOOKUP(9.99999999999999E+307,column_ref,1)
colum_ref: The column from which to lookup the last numeric value.
ADDITIONAL NOTES
Note 1: 9.99999999999999E+307 is the largest number that can be held in a cell.
Note 2: Both of the formulas will ignore any cells with an error and still return the last numeric value in a column, given a numeric value existing in the selected column.