Find last row column cell in an Excel worksheet with VBA

How to find the last row, last column or cell used in an Excel worksheet with VBA using Range.Find method. Whenever we wish to automate the data entry process we should be able to access the last row used in an Excel worksheet or the last column used or the last cell used so that we can enter data in the next blank row, column or cell. Although there are a few methods to achieve this goal, the range.find method appears to be more intuitive and easy to understand and practice.

Watch the training video and then study the code:

Watch this video on YouTube.

Here’s the complete VBA code as used in our example:

(i) How to find the last row with VBA:

Sub findLastRow()
Dim lastRow As Long
lastRow = Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
MsgBox “The last row used in the worksheet is: ” & lastRow
End Sub

(ii) How to find the last column used with VBA:

Sub findLastColumn()
Dim lastColumn As Long
lastColumn = Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
MsgBox “The last column used in the worksheet is: ” & lastColumn
End Sub

(iii) How to find the last cell used:

Sub findLastCell()

MsgBox Range(“A1”).SpecialCells(xlCellTypeLastCell).Address

End Sub

(iv) Code of the recorded macro:

Sub Macro1()

‘ Macro1 Macro
‘ Find last row


Cells.Find(What:=”*”, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
Application.Goto Reference:=”Macro1″
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose
End Sub

Explanation of the Range.Find method step by step:

What:=”*” – The asterisk is a wild card character and tells VBA to find any character or number in a cell. This means, VBA should search for a non-blank cell.

After:=Range(“A1”) – VBA searches for all cells or ranges after Range A1.

LookAt:=xlPart – this part of the VBA code searches for any text or values inside the cell and does not bother about the whole content

LookIn:=xlFormulas – here we wish to search for all non-blank cells. Therefore, if a formula like:

If Range(“B1”)>=90, “Grade A”,”” that can return a blank should also be taken into considertion.

SearchOrder:=xlByRows – here the code searches all the cells in a row from the bottom of the sheet till it finds a cell in a row that has data and gives us the row number used.

SearchDirection:=xlPrevious – the serach for a used cell starts from the last row and from right to left till a cell with data is found

MatchCase:=False – we can ask the VBA code to differentiate between ‘a’ and ‘A’, but this is not necessary in our context.

This may look like too many parameters but the coding still remains intuitive and useful to detremine the last row, last column and last cell used.

Think about this picture when using the above VBA code and things will fall in place:

Range.Find Method connection

Range.Find Method connection

5 thoughts on “Find last row column cell in an Excel worksheet with VBA

  1. Karthik kumar Ghanethy

    Hi Sir,
    Greetings..

    I want to learn VBA I know little basics knowledge about VBA. I need your guidance in learning VBA, request you to please send me any link or suggest any website so that i can learn VBA step by step..

    Regards,
    Karthik.
    Karthikjames_007@rediffmail.com.
    Hyderabad.

    Reply
  2. Karthik kumar Ghanethy

    Hi Sir,
    Greetings..

    If I miss your email I am sending below email please suggest me how to learn VBA.

    I want to learn VBA I know little basics knowledge about VBA. I need your guidance in learning VBA, request you to please send me any link or suggest any website so that i can learn VBA step by step..

    Regards,
    Karthik.
    Karthikjames_007@rediffmail.com.
    g.karthik.bcom@gmail.com
    Hyderabad.

    Reply
  3. Pingback: Create Reports from Raw Data with Loops Auto Filter VBA | Excel VBA Training Videos

  4. Pingback: How to use datediff function with user form | Excel VBA Training Videos

Leave a Reply

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