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.
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.
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
Please leave a comment below with any questions.