non blank value excel – LearnYouAndMe

In this Excel tutorial, you will learn how to get the first and last non-blank cell value in a range.

In Ms SQL Coalesce is function is used to return the first non-null (non blank) value.

Here I have implemented this function using inbuilt Excel functions and using VBA.

I have also shown how you can build a single VBA custom function (VBA UDF) that can return first, last, and Nth non-blank cell value from a range based on the data type.

Get non-blank cell in a range (row or column) using formula

In this section, you will learn how to get first, Last, and Nth non-blank cell values from a range using combination of inbuilt Excel functions.

Get first non-blank cell in a range

Cell C2 formula

=INDEX($A$1:$A$10,MATCH(TRUE,$A$1:$A$10<>””,0),1)

After writing the formula instead of pressing the ENTER key to complete the formula press CTRL+SHIFT+ENTER key to make it an array formula.

Curly brackets “{}” around the formula means successful creation of array formula.

You can use this formula for row range and column range, just modify the range as per your need.

Formula to get last non-blank cell in a range

Cell C2 formula (non array formula)

=LOOKUP(2,1/($A$1:$A$10<>””),$A$1:$A$10)

Alternatively, you also use the below array formula to get the last non blank value

Cell D2 formula (array formula)

=INDIRECT(“A”&MAX(IF($A$1:$A$10<>””,ROW($A$1:$A$10),””)))

After writing the formula instead of pressing the ENTER key to complete the formula press CTRL+SHIFT+ENTER key to make it an array formula.

Formula to get Nth non-blank cell in a range

Cell D2 formula to get Nth non-blank value

=INDIRECT(“A”&SMALL(IF(A1:A10<>””,ROW(A1:A10),””),D1))

In cell D1 which Nth non-blank value you want is written.

In the below image you can see curly brackets “{}” around formula so this is an array formula.

To complete the formula instead of pressing ENTER press CTRL+SHIFT+ENTER to make it an array formula.

Formula Working

For 3rd non-blank SMALL(IF(A1:A10<>””,ROW(A1:A10),””),D1) will output 5.

=INDIRECT (“A” & 5) is equivalent to cell =A5 reference thus outputting value “abhi”.

Suggested Video:- Indirect function (dynamic ranging ) in Excel

VBA UDF function to get non-blank cell in a range | Coalesce VBA UDF

Below is the VBA UDF function named “f_nonBlankVal” which can be used to get non-blank value in a range.

This function is flexible and can output first non-blank value, last non-blank value, Nth non-blank value.

You can also specify whether to consider data type or not while outputting 1st or last or Nth non-blank value.

Don’t worry about code length, just do the below step to create this Coalesce like UDF function in Excel.

Press ALT+F11 to open VBA IDE. In VBA IDE create a new standard module then copy-paste the below code in that module.

Function f_nonBlankVal(rng As Range, Optional instance As Variant = 1, _
Optional dataType As Variant = "")
'LearnYouAndMe.com
'instance 1 > 1st, nth > specified number, L is last
'datatype "" > any type,  n > number, s > string
Dim cel As Range, j As Long, val As Variant
Dim dataMatched As Boolean
j = 1
For Each cel In rng
   Select Case dataType
      Case ""
      dataMatched = True
      Case "n", "N"
      dataMatched = _
      Application.WorksheetFunction.IsNumber(cel.Value)
      Case "s", "S"
      dataMatched = _
      Not (Application.WorksheetFunction.IsNumber(cel.Value))
   End Select
   If dataMatched And cel.Value <> "" Then
      val = cel.Value
         If instance = 1 Then
            f_nonBlankVal = val
            Exit Function
         ElseIf instance = j Then
            f_nonBlankVal = val
            Exit Function
         End If
      j = j + 1
   End If
Next cel
  f_nonBlankVal = val
End Function

You can use f_nonBlankVal VBA function for both row range and column range.

This function has 3 inputs f_nonBlankVal (rng,[instance],[dataType])

rng – Range input having blank and non blank cells

instance – Optional. For 1st non blank value input 1 (default), for last non blank value input “l” or “L” or Nth non-blank value input a number.

dataType – Optional. Default is any data type. To consider only numbers put “n” . To consider only text (string) put “s”.

Get first, last , Nth non-blank from a range using VBA

Cell C2 formula to get the first non-blank cell value

=f_nonBlankVal(A1:A12)

Alternate formula =f_nonBlankVal(A1:A12,1)

Cell C5 formula to get the last non-blank cell value

=f_nonBlankVal(A1:A12,”L”)

Cell C8 formula to get the 3rd non-blank cell value

=f_nonBlankVal(A1:A12,3)

Get Nth non-blank cell from a range based on data type using VBA

Cell C2 formula – 1st non-blank number data type

=f_nonBlankVal(A1:A12,,”n”)

In 2nd input no value is there since default is 1

Cell C5 formula – 1st non-blank text data type

=f_nonBlankVal(A1:A12,,”s”)

Cell C8 formula – last non-blank text data type

=f_nonBlankVal(A1:A12,”L”,”s”)

Cell C11 formula – 2nd non-blank number data type

=f_nonBlankVal(A1:A12,2,”n”)

Here you can clearly see the beauty of VBA UDF, in one function all the scenarios are covered, you just need to take care of 3 inputs.

You may find below topics interesting to learn

How to make Microsoft Excel Add in?

How to lock or protect specific cells (range) in Excel?

How To Convert Number To Hindi Words in Excel?

How to convert string to URL slug in Excel?