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.
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.
Capo.
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
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
Please can you explain why the * character works in this What:=”*” element of code, as opposed to finding first cell that contains it?
Hi Jon: I have been enjoying your VBA course and I have several projects that will require the development of a VBA code. I would like to share with you the code that I have generated to date but my goal is to substitute the hard coding such as the cell definition “C700:P788”. I have included a message box that identifies the cell address and would like to use this information to copy data from one worksheet to another. Since the same type of code will be used throughout this project, I want to make sure that I have a working process before duplicating it. Below is the VBA code that I am currently using and thanks in advance for your support. Please let me know if any other piece of information is required in order to complete the coding process.
Sub RangeFind2()
‘
‘Identifying the last row in the Run time table with data
Sheets(“Run time table”).Select
Range(“B” & Rows.Count).End(xlUp).Select
Selection.Copy
Sheets(“HG1700AG33”).Select
Columns(“B:B”).Select
Selection.Find(What:=”630812″, After:=ActiveCell,
LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
MsgBox ActiveCell.Row
Range(“C788:P788”).Copy
Sheets(“Run time table”).Range(“C213:P213”).PasteSpecial
xlPasteValues
Application.CutCopyMode = False
End Sub