The first used or non-blank cell in an Excel worksheet can be found with the Range.Find method in VBA.

the-range-find-method-to-find-the-first-used-non-blank-cell-with-vba-macro-excel

In this case, we will use the Range.Find method to start in the last cell in the worksheet, and set the SearchDirection parameter to xlNext.  This then starts the search in the first cell in the worksheet (A1) and looks through each row until a non-blank cell is found.

We set the After parameter to Cells(Rows.Count, Columns.Count) to reference the last cell in the sheet. This will find the last cell with the Rows.Count and Columns.count properties, regardless of how many rows and columns are in the sheet.

The LookIn parameter can be set to xlFormulas to find any cell that contains a formula, even if the formula returns a blank.  Set the LookIn parameter to xlValues to only search the values or results of formulas.

Here is the macro code you can copy/paste to your own Excel file.

Sub Find_First_Used_Cell()
'Finds the first non-blank cell in a worksheet.

Dim rFound As Range
    
    On Error Resume Next
    Set rFound = Cells.Find(What:="*", _
                    After:=Cells(Rows.Count, Columns.Count), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
    
    On Error GoTo 0
    
    If rFound Is Nothing Then
        MsgBox "All cells are blank."
    Else
        MsgBox "First Cell: " & rFound.Address
    End If
    
End Sub

I have an entire video series and article on how to find the LAST used or non-blank cell on a sheet with VBA.

Please leave a comment below with any questions.

3 comments

Your email address will not be published. Required fields are marked *

  • If you’re trying to find the first row with data, then just add “.Row” at the end of cells.find; example: Cells.Find(What:=”*”, _
    After:=Cells(Rows.Count, Columns.Count), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False).Row

  • Hi Jon,

    I am a new VBA user. I tried your code above and it worked. Thank you. Would you please modify it to go to the top row in an Excel sheet no matter what is in a row.

    Thanks,

    Gary

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly