How to return the first / last non blank cell in a row or column?

In day-to-day Excel work, it's common to encounter datasets where only some cells contain values, while others are blank. Often, you may need to quickly identify either the very first or the very last filled cell in a particular row or column. While this is effortless with a handful of entries, in longer rows or columns, manually searching for these values becomes inefficient, time-consuming, and prone to error.

There are several practical ways to efficiently retrieve the first or last non blank cell value in a row or column, helping you automate this task and save valuable time. Below, you will find both formula-based and other hands-on solutions suitable for different real-life scenarios, including handling dynamic data ranges or large datasets.

Return the first non blank cell in a row or column with formula

Return the last non blank cell in a row or column with formula

Return the first or last non blank cell value with VBA macro

Find the first or last non blank cell by using Excel's Filter function

Return the first non blank cell in a row or column with formula

To extract the first cell containing data from a row or column, you can use a combination of INDEX and MATCH functions. This approach applies when you wish to retrieve the value programmatically, keeping your worksheet dynamic as underlying data changes. Here’s how to do it:

1. In a blank cell next to your data range, enter the following formula:

=INDEX(A1:A13,MATCH(TRUE,INDEX((A1:A13<>0),0),0))

For example, if your data is in cells A1 to A13, this formula will return the first non blank and non-zero cell. See example below:

2. Press the Enter key. The value of the first cell in the range that is not blank (or not zero, based on the formula logic) will be displayed like this:

Notes & Tips:

• In the formula above, you can adjust A1:A13 to reference any column or to reference a row (such as 1:1 for row 1, or B2:M2 for a subsection of a row).
• This method works reliably on a single row or single column. For tables or multi-selection ranges, consider applying the formula to each row or column individually.
• If the formula returns an error (#N/A), double-check that your range actually contains at least one non blank, non-zero cell.
• Remember, for true blanks (""), replace <>0 with <>"" if you wish to ignore only empty cells rather than zeros.

Unlock Excel Magic with Kutools AI

• Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
• Custom Formulas: Generate tailored formulas to streamline your workflows.
• VBA Coding: Write and implement VBA code effortlessly.
• Formula Interpretation: Understand complex formulas with ease.
• Text Translation: Break language barriers within your spreadsheets.

Enhance your Excel capabilities with AI-powered tools. Download Now and experience efficiency like never before!

Return the last non blank cell in a row or column with formula

To get the value from the last non blank cell in a given range, an array-based LOOKUP formula provides an efficient, straightforward solution for dynamic or changing data. This is especially useful for automatically identifying the latest data entry in a list or summary table.

1. Type the following formula into a blank cell beside your target range:

=LOOKUP(2,1/(A1:A13<>""),A1:A13)

This formula scans the specified range and returns the value of the last cell that is not empty. For example, using A1:A13 as the range:

2. After pressing Enter, Excel will calculate and display the value in the last non blank cell:

Notes & Suggestions:

• You can use this formula with any single column or row (B1:B20, F8:F30 or 2:2 etc.). Update the range reference as needed.
• If your data includes zeros you'd like to ignore, you can replace A1:A13<>"" with A1:A13<>0 but take care that true blank versus zero matches your intent.
• This approach works best for straightforward data ranges. For ranges with formulas returning "" (empty text), this formula considers such cells as blank.
• Should all cells be blank, the formula will return an #N/A error.

Return the first or last non blank cell value with VBA macro

For users working with large datasets or needing to automate repetitive tasks, a simple VBA macro can greatly streamline the process—especially when ranges vary or involve many rows or columns. Unlike formulas, VBA performs on-demand actions like finding the first or last non-blank cell, making it ideal for repeated operations across multiple ranges.

1. Open the VBA editor by going to Developer > Visual Basic. In the VBA window that appears, click Insert > Module, and paste one of the following procedures into the module window:

Macro to find the first non blank cell in a selected range:

Sub FindFirstNonBlankCell()
    Dim rng As Range
    Dim cell As Range
    Dim firstValue As Variant
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
    
    firstValue = ""
    
    For Each cell In rng
        If cell.Value <> "" Then
            firstValue = cell.Value
            Exit For
        End If
    Next cell
    
    If firstValue <> "" Then
        MsgBox "The first non blank cell value is: " & firstValue, vbInformation, xTitleId
    Else
        MsgBox "No non blank cells found.", vbExclamation, xTitleId
    End If
End Sub

Similarly, here is the code to find the last non blank cell:

Sub FindLastNonBlankCell()
    Dim rng As Range
    Dim cell As Range
    Dim lastValue As Variant
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
    
    lastValue = ""
    
    For Each cell In rng
        If cell.Value <> "" Then
            lastValue = cell.Value
        End If
    Next cell
    
    If lastValue <> "" Then
        MsgBox "The last non blank cell value is: " & lastValue, vbInformation, xTitleId
    Else
        MsgBox "No non blank cells found.", vbExclamation, xTitleId
    End If
End Sub

2. To execute the code, click the Run button in the VBA editor. You will be prompted to select the target range to search for non blank cells. After making your selection and confirming, a dialog box will display either the first or last non blank cell value depending on which macro you run.

• These macros are flexible and suitable for both rows and columns, regardless of data size.
• VBA allows for automation and repeatable processing, making it ideal for frequent or large-scale tasks.
• When running macros, make sure to save your workbook and enable macros as needed. Always test on sample data to verify accuracy before applying to important data.

Find the first or last non blank cell by using Excel's Filter function

For users who need a quick visual way to identify non blank values—especially with very large columns of data—the built-in Filter feature in Excel can help spotlight non blank entries at a glance. While this method will not automatically return the value to another cell, it is highly effective for review or navigation during data analysis.

Here's how to visually locate the first or last non blank cells through filtering:

1. Select the column or row that contains your data. You may select the entire column (e.g., click on the column letter) for easier filtering.
2. Click on the Data tab, then choose Filter.
3. Click the small filter arrow in the header of your range or table.
4. Uncheck the (Blanks) option so that only filled cells remain visible.
5. After filtering, the first visible value at the top of the column is your first non blank cell; scroll to the bottom to see the last.

Advantages: The filter method is quick, requires no formulas, and works well even for columns with thousands of rows.
Disadvantages: The solution is visual only—it doesn't output the result to a cell or support automation as formulas and VBA do. Still, it is ideal for manual checks, reviews, and interactive data exploration.

Troubleshooting & Suggestions:
If the filter seems not to work, ensure you have not selected only part of your data, as this may cause the filter to apply incorrectly. Remove filters when finished by clicking Data > Clear to restore your full dataset view.