How to Find the First Used Cell with a VBA Macro - Excel Campus
1

How to Find the First Used Cell with a VBA Macro

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.

Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 1 comments
weimar - October 5, 2017

Capo.

Reply

Leave a Reply: