Return last numeric value in a row
To return the last numeric value in a row we can apply two methods using an Excel HLOOKUP function or a combination of an Excel INDEX and MATCH functions
Example: Return last numeric value in a row
The formula uses the Excel INDEX anf MATCH functions to return the last numeric value in row 4.
|
METHOD 2. Return last numeric value in a row using the Excel HLOOKUP function
EXCEL
=HLOOKUP(9.99999999999999E+307,4:4,1)
|
The formula uses the Excel HLOOKUP function to return the last numeric value in row 4.
|
Dim ws As Worksheet
ws.Range("C6").Value = Application.WorksheetFunction.Index(ws.Range("4:4"), Application.WorksheetFunction.Match(9.99999999999999E+307, ws.Range("4:4")))
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 ("C6") in the VBA code to any cell in the worksheet.
Row Reference: Select the row that you are searching through for the last numeric value by changing the row reference ("4:4") in the VBA code to any row in the worksheet, that doesn't conflict with the formula.
METHOD 2. Return last numeric value in a row using the Excel HLOOKUP function
VBA
Dim ws As Worksheet
ws.Range("C6").Value = Application.WorksheetFunction.HLookup(9.99999999999999E+307, ws.Range("4:4"), 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 ("C6") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Row Reference: Select the row that you are searching through for the last numeric value by changing the row reference ("4:4") in the VBA code to any row in the worksheet, that doesn't conflict with the formula.
To return the last numeric value in a row we can apply two methods using an Excel HLOOKUP function or a combination of an Excel INDEX and MATCH functions.
=INDEX(row_ref,MATCH(9.99999999999999E+307,row_ref))
=VLOOKUP(9.99999999999999E+307,row_ref,1)
row_ref: The row 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 row, given a numeric value existing in the selected row.