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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

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

  1. 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.

  2. 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.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.