Return value of first non-blank cell in a range
To return value of the first non-blank cell in a range we can apply both Excel and VBA methods
Example: Return value of first non-blank cell in a range
|
The formula returns the value of the first non-blank cell in the selected range. This formula will only work if the range only comprises a single column (with multiple rows) of single row (with multiple columns).
|
METHOD 2. Return value of first non-blank cell in a range using array formula
EXCEL
|
The formula returns the value of the first non-blank cell in the selected range. This formula will only work if the range only comprises a single column (with multiple rows) of single row (with multiple columns). This is an array formula, therefore to make it work after typing the formula you need to press Ctrl + Shift + Enter at the same time. This will add { } into around the formula.
|
Dim ws As Worksheet
ws.Range("G5").Formula = "=INDEX(B5:B11,MATCH(TRUE,INDEX((B5:B11<>""""),0),0))"
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 ("G7") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Range: Select the range that you want to search through for the first non-blank cell by changing the range ("B5:B11") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
METHOD 2. Return value of first non-blank cell in a range using VBA with an array formula
VBA
Dim ws As Worksheet
ws.Range("G5").FormulaArray = "=INDEX(B5:B11,MATCH(FALSE,ISBLANK(B5:B11),0))"
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 ("G7") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Range: Select the range that you want to search through for the first non-blank cell by changing the range ("B5:B11") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.