Bottom line: Learn how to find the last row, column, or cell in a worksheet using three different VBA methods.  The method used depends on the layout of your data, and if the sheet contains blank cells.

Skill level: Intermediate

VBA Methods to Find the Last Cell Row Column in Excel

Video: 3 Part Series How to Find the Last Cell with VBA

Video best viewed in full screen HD

Download the file that contains the code:

Find Last Cell VBA Example.xlsm (79.6 KB)

Jump to the Code Examples on this page:

Finding the Last Cell is All About the Data

Finding the last used row, column, or cell is one very common task when writing macros and VBA applications.  Like anything in Excel and VBA, there are many different ways to accomplish this.

Choosing the right method mostly depends on what your data looks like.

In this article I explain three different VBA methods of the Range object that we can use to find the last cell in a worksheet.  Each of these methods has pros and cons, and some look scarier than others. 🙂

But understanding how each method works will help you know when to use them, and why.

#1 – The Range.End() Method

The Range.End method is very similar to pressing the Ctrl+Arrow Key keyboard shortcut.  In VBA we can use this method to find the last non-blank cell in a single row or column.

VBA Range.End(xlUp) Method to Find Last Non-Blank Row

Range.End VBA Code Example

Sub Range_End_Method()
'Finds the last non-blank cell in a single row or column

Dim lRow As Long
Dim lCol As Long
    
    'Find the last non-blank cell in column A(1)
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    'Find the last non-blank cell in row 1
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    MsgBox "Last Row: " & lRow & vbNewLine & _
            "Last Column: " & lCol
  
End Sub

Download the file that contains the code:

Find Last Cell VBA Example.xlsm (79.6 KB)

To find the last used row in a column, this technique starts at the last cell in the column and goes up (xlUp) until it finds the first non-blank cell.

The Rows.Count statement returns a count of all the rows in the worksheet.  Therefore, we are basically specifying the last cell in column A of the sheet (cell A1048567), and going up until we find the first non-blank cell.

It works the same with finding the last column.  It starts at the last column in a row, then goes to the left until the last non-blank cell is found in the column.  Columns.Count returns the total number of columns in the sheet.  So we start at the last column and go left.

The argument for the End method specifies which direction to go.  The options are: xlDown, xlUp, xlToLeft, xlToRight.

Pros of Range.End

  • Range.End is simple to use and understand since it works the same way as the Ctrl+Arrow Key shortcuts.
  • Can be used to find the first blank cell, or the last non-blank cell in a single row or column.

Cons of Range.End

  • Range.End only works on a single row or column.  If you have a range of data that contains blanks in the last row or column, then it may be difficult to determine which row or column to perform the method on.
  • If you want to find the last used cell then you have to evaluate at least two statements.  One to find the last row and one to find the last column.  You can then combine these to reference the last cell.

Here are the help articles for Range.End

#2 – The Range.Find() Method

Range.Find VBA Method When Data Contains Blanks

The Range.Find method is my preferred way to find the last row, column, or cell.  It is the most versatile, but also the scariest looking.  🙂

Range.Find Method Arguments VBA

Range.Find has a lot of arguments, but don't let this scare you.  Once you know what they do you can use Range.Find for a lot of things in VBA.

Range.Find is basically the way to program the Find menu in Excel.  It does the same thing, and most of the arguments of Range.Find are the options on the Find menu.

Range.Find Same As Find Window in Excel

Range.Find Code Example

The following is the code to find the last non-blank row.

Sub Range_Find_Method()
'Finds the last non-blank cell on a sheet/range.

Dim lRow As Long
Dim lCol As Long
    
    lRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
    
    MsgBox "Last Row: " & lRow

End Sub

Download the file that contains the code:

Find Last Cell VBA Example.xlsm (79.6 KB)

The Range.Find Method Explained

The Find method is looking for the first non-blank cell (“*”).  The asterisk represents a wildcard character that looks for any text or numbers in the cell.

Starting in cell A1, it moves backwards (xlPrevious) and actually starts it's search in the very last cell in the worksheet.  It then moves right-to-left (xlByRows) and loops up through each row until it finds a non-blank cell.  When a non-blank is found it stops and returns the row number.

Range.Find Method to Find Last Row Explained - Excel VBA

Here is a detailed explanation for each argument.

  • What:=”*” – The asterisk is a wildcard character that finds any text or number in the cell.  It's basically the same as searching for a non-blank cell.
  • After:=Range(“A1”) – Start the search after cell A1, the first cell in the sheet.  This means that A1 will NOT be searched.  It will start the search after A1 and the next cell it searches depends on the SearchOrder and SearchDirection.  This argument can be changed to start in a different cell, just remember that the search actually starts in the cell after the one specified.
  • LookAt:=xlPart – This is going to look at any part of the text inside the cell.  The other option is xlWhole, which would try to match the entire cell contents.
  • LookIn:=xlFormulas – This tells Find to look in the formulas, and it is an important argument.  The other option is xlValues, which would only search the values.  If you have formulas that are returning blanks (=IF(A2>5,”Ok”,””) then you might want to consider this a non-blank cell.  Specifying the LookIn as xlFormulas will consider this formula as non-blank, even if the value returned is blank.
  • SearchOrder:=xlByRows – This tells Find to search through each entire row before moving on to the next.  The direction is searches left-to-right or right-to-left depends on the SearchDirection argument.  The other option here is xlByColumns, which is used when finding the last column.
  • SearchDirection:=xlPrevious – This specifies which direction to search.  xlPrevious means it will search from right-to-left or bottom-to-top.  The other option is xlNext, which moves in the opposite direction.
  • MatchCase:=False – This tells Find not to consider upper or lower case letters.  Setting it to True would consider the case.  This argument isn't necessary for this scenario.

Ok, I know that's a lot to read, but hopefully you will have a better understanding of how to use these arguments to find anything in a worksheet.

Pros of Range.Find

  • Range.Find searches an entire range for the last non-blank row or column.  It is NOT limited to a single row or column.
  • The last row in a data set can contain blanks and Range.Find will still find the last row.
  • The arguments can be used to search in different directions and for specific values, not just blank cells.

Cons of Range.Find

  • It's ugly.  The method contains 9 arguments.  Although only one of these arguments (What) is required, you should get in a habit of using at least the first 7 arguments.  Otherwise, the Range.Find method will default to your last used settings in the Find window.  This is important.  If you don't specify the optional arguments for LookAt, LookIn, and SearchOrder then the Find method will use whatever options you used last in Excel's Find Window.
  • Finding the last cell requires two statements.  One to find the last row and one to find the last column.  You then have to combine these to find the last cell.

Macro Recorder to the Rescue!

Range.Find is still my preferred method for finding the last cell because of it's versatility.  But it is a lot to type and remember.  Fortunately, you don't have to.

You can use the macro recorder to quickly create the code with all the arguments.

  1. Start the macro recorder
  2. Press Ctrl+F
  3. Then press the Find Next button

The code for the Find method with all the arguments will be generated by the macro recorder.

Use a Custom Function for the Find Method

You can also use a custom function (UDF) for the find method.  Ron de Bruin's Last Function is a perfect example.  You can copy that function into any VBA project or code module, and use it to return the last row, column, or cell.

I also have a similar function in the example workbook.  My function just has additional arguments to reference the worksheet and range to search in.

Here are the help articles for Range.Find

#3 – Range.SpecialCells(xlCellTypeLastCell)

The SpecialCells method does the same thing as pressing the Ctrl+End keyboard shortcut, and selects the last used cell on the sheet.

VBA Range.SpecialCells Method to Find Last Used Cell in Excel

SpecialCells(xlCellTypeLastCell) Code Example

Sub Range_SpecialCells_Method()

    MsgBox Range("A1").SpecialCells(xlCellTypeLastCell).Address
      
End Sub

Download the file that contains the code:

Find Last Cell VBA Example.xlsm (79.6 KB)

It's actually the easiest way to find the last used cell.  However, this method is finding the last used cell, which can be different than the last non-blank cell.

Often times you will hit Ctrl+End on the keyboard and be taken to some cell way down at the end of the sheet that is definitely not used.  This can occur for a number of reasons.  One common reason is the formatting properties for that cell have been changed.  Simply changing the font size or fill color of a cell will flag it as a used cell.

Pros of Range.SpecialCells

  • You can use this method to find “used” rows and columns at the end of a worksheet and delete them.  Comparing the result of Range.SpecialCells with the result of the Range.Find for non-blanks can allow you to quickly determine if any unused rows or columns exist on the sheet.
  • Deleting unused rows/columns can reduce file size and make your scroll bar bigger.

Cons of Range.SpecialCells

  • Excel only resets the last cell when the workbook is saved.  So if the user or macro deletes the contents of some cells, then this method will not find the true last cell until after the file is saved.
  • It finds the last used cell and NOT the last non-blank cell.

Other Methods for Finding the Last Cell

Well, that should cover the basics of finding the last used or non-blank cell in a worksheet.  If you sheet contains objects (tables, charts, pivot tables, slicers, etc.) then you might need to use other methods to find the last cell.  I will explain those techniques in a separate post.

I also have an article on how to find the FIRST used cell in the worksheet.

Please leave a comment below if you have any questions, or are still having trouble finding the last cell.  I'll be happy to help! 🙂

131 comments

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

  • Thank you for this great tutorial! Everything works perfectly, however I’m having trouble setting the last row correctly if there are cells with a formula in it. What I mean is, I want the last row to be set at lets say 8. However, there are cells below row 8 that are blank but have hidden formulas. i.e. one may contain =IFERROR(VLOOKUP(F8,’dt-attext’!A$2:J$33,10,0),””) or something similar and will appear blank, but since the formula is still technically in place, that row is counted when I want it to stop at a row with visible data. Anything would help! Thank you!

  • what if my range has 4 rows Ex. A1:D4, and i want to copy it to my other datatable with the same row numbers? but i havent found any solution, whenever i run my function, they all paste in the same row, please help me!
    Sub UpdateLogWorksheet()

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range

    ‘cells to copy from Input sheet – some contain formulas
    myCopy = “A1:D4”

    Set inputWks = Worksheets(“Input”)
    Set historyWks = Worksheets(“datatbl”)

    With historyWks
    nextRow = .Cells(.Rows.Count, “A”).End(xlUp).Offset(1, 0).Row
    End With

    With inputWks
    Set myRng = .Range(myCopy)

    If Application.CountA(myRng) myRng.Cells.Count Then
    MsgBox “Please fill in all the cells!”
    Exit Sub
    End If
    End With

    With historyWks
    With .Cells(nextRow, “A”)
    .Value = Now
    .NumberFormat = “mm/dd/yyyy hh:mm:ss”
    End With
    .Cells(nextRow, “B”).Value = Application.UserName
    oCol = 3
    For Each myCell In myRng.Cells
    historyWks.Cells(nextRow, oCol).Value = myCell.Value
    oCol = oCol + 1
    Next myCell
    End With

    ‘clear input cells that contain constants
    With inputWks
    On Error Resume Next
    With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
    .ClearContents
    Application.GoTo .Cells(1) ‘, Scroll:=True
    End With
    On Error GoTo 0
    End With
    End Sub

  • I have to link table with Customised values of rows & coloumns.
    e.g if I put row= 5 & coloumn=10 (this value I put in particular cell in sheet 1 then I want to create table with this entered values in sheet 2

    can you please help me with this question

  • Thank you for informative tutorial. When I use the code below by itself it works perfectly but when I try to call or add it into my code it gives me the error 91 saying that I need to set the object. I tried simply putting “set lRow =” but the error message still appears. I am fairly new to programming on Excel so I am not sure exactly what to do to still use this great code. If you have any ideas I would love to hear them. Thank you.

    Sub Last_Row()
    Dim lRow As Long
    Dim lCol As Long

    lRow = Cells.Find(What:=”*”, _
    After:=Range(“A1”), _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row

    MsgBox (“Last Row: ” & lRow)

    End Sub

  • Hi,
    Thanks for the video, I enter details on sheet1, on a click details would be saved onto sheet2. I want to check last value in a column A from sheet2 and increment it +1 and add it to serail number column in sheet1. Could you please assist me on this

    Regards,
    Raghav

  • Hi,
    Thanks for the video, I enter details on sheet1, on a click details would be saved onto sheet2. I want to check last value in a column A from sheet2 and increment it +1 and add it to serail number column in sheet1. Could you please assist me on this

    Regards,
    Raghav

  • Hi,
    With below code I am not able to find last used cell in the column or sheet..

    lRow = Cells(Rows.Count, 1).End(xlUp).Row

    Kindly help..
    Thanks..

  • Hei,

    Thank you for a very good and intuitive blog, could you please help me out?

    I have used a function for a VLookup. My objective is to obtain data from one spreadsheet and use that to automatically update the next available cell to the right. This is meant to be a dynamic Vlookup but one line keeps giving me problems.

    This line “Set rng = shInpt.Range(Cells(8, Columns.Count).End(xlToLeft).Offset(0, 1) & shInpt.Cells(Rows.Count, “A”).End(xlUp).Row)”

    Run-Time Error ‘1004’
    Application-Defined or object-defined error.

    This is the whole syntax.

    Public Sub AutoUpdate()

    Dim shInpt As Worksheet
    Dim shDesc As Worksheet

    Set shInpt = ThisWorkbook.Worksheets(“Input”)
    Set shDesc = ThisWorkbook.Worksheets(“Descriptive Statistics”)

    ‘Update Values Input Sheet
    ‘A-Customers Dynamic Vlookup
    Dim rng As Range
    Dim rCell As Range, pMatrix As Range

    pSearchCol = shInpt.Range(“A8:A8”).Column
    Set pMatrix = shDesc.Range(“$A6:$C” & shDesc.Cells(Rows.Count, “C”).End(xlUp).Row)
    pMatColumn = 3

    Set rng = shInpt.Range(Cells(8, Columns.Count).End(xlToLeft).Offset(0, 1) & shInpt.Cells(Rows.Count, “A”).End(xlUp).Row)

    For Each rCell In rng.Cells
    With rCells
    rCell.Value = fsVlookup(shInpt.Cells(rCell.Row, pSearchCol), pMatrix, pMatColumn)
    End With
    Next rCell

    ‘Date stamp Input sheet
    With shInpt
    .Range(“B4”).End(xlToRight).Offset(0, 1) = DynamicDate
    .Range(“A6”).End(xlToRight).Offset(0, 1) = shDesc.Range(“B6”).Value
    End With

    ‘Antall Treff (A – Customers)
    shInpt.Range(“A7”).End(xlToRight).Offset(0, 1).Value = shInpt.Range(“A8”).End(xlToRight)

    End Sub

  • Hi,
    How to select a last cell (containing data) from the non selected sheet?
    I tried for Sheets(“Sheet1”).Range(Sheets(“Sheet1”).Cells(“A1”)).End(xlDown).Select
    But this is not working.
    I need to use this continuously and hence want to avoid selecting the sheet. This will work if I select the sheet prior selecting the last cell command.

    Can you please help me to select the last cells without selecting the sheet in VBA?

  • Hi good day
    i really enjoy using your videos to help me understand vba more
    i have a question
    i have a sheet and i want to delete some data that i dont really need
    i want to delete all the row between the cells that contains the number 0000100 to the cell that contains the word Street everything in between those two cells i want to be deleted
    I have tried a few things but keep getting errors

    please help
    thanks in advance

  • Hi. I have a related programming impasse. I am looking to select (and paste the clipboard contents to) the blank cell just below the last non-blank cell in a range. The range is B2:B501. The clipboard contains values in a horizontal range P4:AB4, so I am wanting to populate B2:N2 the first time the macro executes, then B3:N3 the next time, and so on, up to 500 times when the range is filled.

    I had it working for a while but then something happened and it stopped working after about the 20th time and started putting the clipboard on top of B2:N2 every time after that. Can you help? Thank you

  • This is a great tutorial. Thanks, Jon.

    What I’m trying to do is count, for a given row, how many cells have data in them. When I use the code for lcol above, it returns the address of the last cell with data (for example, D:D). Is there a way for it to return a count, or a way to convert an address such as D:D into a count?

    Many thanks for the expert help!

    Jean

    • Hi Jean,

      Great question! Yes, there are a few ways to do it. We can use the COUNT or COUNTA worksheet functions for this. Here is some sample code.

      lCount = Application.WorksheetFunction.CountA(ActiveSheet.Range("D:D"))

      I hope that helps.

  • With Worksheets(2).Activate
    ‘Find the last non-blank cell in column A(1)
    lRow = Cells(Rows.Count, 1).End(xlUp).Row

    ‘Find the last non-blank cell in row 1
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Range(lRow:lCol).Select <— error here
    Selection.Copy
    .
    . rest of the code
    .
    .

    Can u tell me how to do this?

    • Hi Shweta,
      You need to change it to:
      Cells(lrow,lcol)
      your variables lrow and lcol are getting filled with a number (integer)
      to use the Range Property you need to fill it with a String or direct address like “A2”

      suppose lrow = 5 and lcol = 3 you are commanding vba to select Range(5:3) which is invalid.

      with Cells you give it coordinates i.e. row 5, column 3

  • Hi Jon,

    Could you help me how i can use replace command.
    .(dot) needs to be replaced with – whenever there is a .(dot) in cell

    For ex: abc.def needs to be replaced with abc-def

    Thank you
    Abdul

  • Hi very good tutorial, iam new in excel.
    after search last row i need to paste text from another sheet after last row but not a message like in your tutorial. Can you help me, please?

    • Thanks Vilius! Great question! Once you find the last row, you can reuse the variable in a reference to a range. There are many ways to do this. Here is one example that would reference the last row in column A. This assumes the last row number is set in a variable named lRow in a line of code above.

      Range("A" & lRow).PasteSpecial

      To reference the row below the last row you could use either of the following.

      Range("A" & lRow + 1).PasteSpecial
      Range("A" & lRow).Offset(1, 0).PasteSpecial

      The Offset property allows you to offset a specific number of rows or columns. In this case we are offsetting 1 row down and 0 columns to the right of the range reference.

      Checkout my video series on copy and paste with VBA for different ways to copy & paste.

      I hope that helps get you started.

      • I know this is very basic and the answer is clear. I just can’t make it work when duplicating your lesson. To reference the result in a cell I get run time error 1004. As you will guess I’m a novice!
        My code
        Sub CompanyRankings()
        Workbook(“REPORT”).Worksheets(“Report”).Select
        lRow =Cells(Rows.Count, 2).End(xlUp).Row
        For Counter =12 to lRow

        Range(“A” & lRow).PasteSpecial. ‘This is where the error happens

        Next Counter
        End Sub

        Do you know why it would give the error?

        Thanks,

        Gareth

        • Hi Gareth,

          Two things I see here. The Workbook(“REPORT”) references needs to be changed to Workbooks(“REPORT”). The Workbooks property is plural.

          You’ll want to make sure lRow is returning a number greater than zero. If it is returning a zero then you will get an error. You can step through the code with F8 on the keyboard to test each line.

          I don’t see where you are copying anything in this macro. An error will also be hit if there is nothing copied to the clipboard first with the Copy method. That line of code will have to be above the PasteSpecial line.

          I hope that helps.

  • Hi there

    Need to find last row without data in it. Input data through formula (Assigning macro to a selfmade button) but it wont go to row under last row with data…

    ‘ Find den sidste ledige række i arket

    eRow = Ark1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    ‘ Insæt data i hver celle ud fra input boks

    Set NextCell = Cells(Rows.Count, “B”).End(xlUp)
    If NextCell.Row > 1 Then NextCell = NextCell.Offset(1, 0)

    Answer = InputBox(“Indtast Navn”)
    If Answer = “” Then Exit Sub
    NextCell = Answer

    Can u please help me with this?

  • this formula wouldn’t paste in the next blank. your help would be much appreciated.

    Sub append()

    Dim i As Integer
    Dim erow As Integer

    For i = 5 To 10
    erow = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Sheets(1).Range(“A” & i).Copy
    Sheets(2).Range(“B” & erow).PasteSpecial xlPasteValuesAndNumberFormats
    Next i
    End Sub

    • Hi James,

      erow is returning the last row in the first column. I believe this should be the second column since you are pasting in column B. So you will need to change the column index number in the cells property to 2.

      Cells(Rows.Count, 2)

      I hope that helps. Thanks!

  • Hi Jon,

    In my excel sheet, i have data from A1 to C10. Now I am sending this to my access table using DoCmd.TransferSpreadsheet. I am able to send the data successfully if i specify the range.
    But i want to make the script dynamic and instead of giving C10 i want to send data till end of the cell that has data. How can i achieve this.

  • Hie Jon

    This code is not picking the values to insert to the worksheet from a user form

    Cells(lastrow + 1, 1).Value = txtFname.Text

    what might be the problem

  • Hi John,

    I have a question regarding finding the first row in a table and inserting data. I have used below function in my code which inserts data to the lastrow in a table. Please let me know how do i change it so that the data is inserted at the first row.

    Private Sub BtnAdd_Click()
    Dim lastrow As Long

    lastrow = Sheets(“Sheet1”).Range(“B” & Rows.Count).End(xlUp).Row
    Cells(lastrow, “B”).Value = txtdt.Text
    Cells(lastrow, “C”).Value = txtamt.Text
    Cells(lastrow, “D”).Value = txtper.Text
    End Sub

    Regards,
    Nousiba

  • Not sure why but I can’t seem to use the Find method with optional parameters.
    This statement works:
    Set foundCell = UsedRange.Find(“*”)
    But any optional parameter throws errors:
    Set foundCell = UsedRange.Find(“*”,afterCell,xlByRows) – subscript out of range
    Set foundCell = UsedRange.Find(What:=”*”,After:=afterCell,SearchMethod:=xlByRows) – Expected ‘)’
    Set foundCell = UsedRange.Find “*”,afterCell,xlByRows – Expected end of statement

    What am I doing wrong?

    • Hi Linda,
      When using optional parameters you will need to specify the parameter name then use the colon equal sign := then the parameter value. I believe the After parameter is wrong. I’m not sure what afterCell is. You might want ActiveCell.

      I hope that helps.

    • Ok great! You could also use the SpecialCells method to find the Constants or Formulas that contain numbers.

      Cells.SpecialCells(xlCellTypeConstants, xlNumbers)

      That would return a range of all the cells that contain numbers.

  • The below formula finds the last used row and column within a sheet but I want to select their outputs for a range formula to transpose with. Have you any idea how I could do that? Thanks

    Dim lRow As Long
    Dim lCol As Long

    ‘Determines last used row

    lRow = Cells.Find(What:=”*”, _
    After:=Range(“A1″), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row

    ‘Determines last used column

    lCol = Cells.Find(What:=”*”, _
    After:=Range(“A1”), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Column

    MsgBox “The Last Row used is: ” & lRow & vbNewLine & “The Last Column used is: ” & lCol

    • Hi Lloyd,
      I’m not sure I understand your question. Do you want to use the results in a formula in the spreadsheet?

      Here is a very simple example of how to use the last row variable in a formula with VBA.

      Range("A1").Formula = "=SUM(A2:A" & lRow & ")"

      The formula is a string that can be concatenated with variable values using the ampersand symbol. I hope that helps get you started. Thanks!

  • I love your tutorial! It is very helpful as I am just learning macros. I am in a bind and need your help, however. I wanted to use the SpecialCells method you talked about here, and I wanted to make it select the entire range so that I can run another macro that I have that deletes all blank rows. Can you demonstrate how to do that? Normally I go in and manually navigate to the end of the worksheet and select all the columns and rows that have information along with blank rows, and then I run my macro that deletes all the blank rows. Here is the macro I use to delete blank cells:

    Sub DeleteBlankRows5()
    Dim aRow As Range
    Dim BlankRows As Range
    
      For Each aRow In Selection.Rows.EntireRow
         If WorksheetFunction.CountA(aRow) = 0 Then
              If Not BlankRows Is Nothing Then
                   Set BlankRows = Union(BlankRows, aRow)
              Else
                   Set BlankRows = aRow
              End If
         End If
      Next
      If Not BlankRows Is Nothing Then
         Application.ScreenUpdating = False
         BlankRows.Delete
         Application.ScreenUpdating = True
      End If
    End Sub
    • Hi Gerard,
      Thanks for the nice feedback. For that you might want to use the UsedRange property. The following line of code will select all the used cells in the worksheet.

      ActiveSheet.UsedRange.Select

      Try it out and let me know if you have any question. Thanks!

  • Hi, good morning. I have a table which contain a lot of data. Some data are same but just different in the time key in. I am using a userform input to find the latest data in the database. for example,

    123 4:07:56 PM
    999 4:18:28 PM
    123 4:18:36 PM <–
    765 9:28:34 AM

    i want to find the data in the third cell.
    Can I have any ideas?

    Thanks
    rick

  • Hi John,

    I have a dilemma and seeking your help if you will. I’m working on a piece of code that will drag down some formulas that I’ve inserted into new columns to fill in the columns. The problem is that it’s dragging all the way down to the end of the sheet and not the end of the data set. I understand why it’s doing this, I just can’t figure out how to get it to drag down and stop at the last row of the columns with data. Should I be using “.End(xlDown)”? If so, would I have to use this for each column separately and not as a range? Please help. Code below:

    ‘Selects formula in C2:E2 and drags them down
    Range(“C2:E2”).Select
    Selection.AutoFill Destination:=Range(Selection, SpecialCells(xlCellTypeLastCell))

    • Hi John,
      It sounds like the last used cell on the sheet might have been accidentally set to the last row of the sheet. This is why I tend to use the Range.Find method instead of SpecialCells. If you don’t want to use Range.Find, you can probably reset the last used cell by deleting all the rows below the actual last used row on the sheet. Then save the workbook. After you save, you should be able to press Ctrl+End to select the actual last used cell on the sheet. I hope that helps.

  • HI jon

    Just i need a small macro.I dealing with big data in Clinical Research.
    If Possible can you please provide your mail Id, i will FWD the attached file details

    Regards
    steve

  • Hi Jon,

    Please I am struggling to find a way to calculate values in two columns A and B. with this formula
    Area = D / I x Eff1 x Eff2
    and values of D and I are in A and B columns. I want the output values of ‘Area’ in column C. Your help on this will be much appreciated.

    Thanks,
    Chris.

  • Hi John,
    As soon as this LastCell Function appeared in one of your earliest modules, I copied it out and played with it in my own workbooks just to understand it better. I’ve always had trouble using the Find methods.

    Now to be directed to your detailed explanation of the Range Find method is a real treat. And to see your exchanges with Zack and other Excel masters here is a double scoop of ice cream!

    That said, seeing all the nuances and tangles you could have touched, I’m all the more impressed your judgment in choosing what to include and exclude in your foundation course and this article.

    Rick

    • Hi Rick,

      Thank you so much for the nice feedback. I really appreciate. 🙂

      I agree that the Find Method can be a bit of a monster when we first look at it, but I think you will find a lot of uses for it in the future. It’s a great one to have in your tool belt.

      It’s always a treat to have Zack stop by. He is an absolute master and I have learned a lot from him, and all the other members of this community that leave comments. It’s great to learn from everyone and see how they are using Excel.

      Thanks again Rick!

  • Hello,

    I am trying to use check boxes in order to copy and paste values over to another worksheet automatically.

    This means multiple items need to append onto the other worksheet leaving a row between eachother.

    Is there something i can do to make this work?

    this is the formula i am currently using, but instead of having a specified area of selection when copying too, i would like it to pick up the row below the last used row

    Private Sub CheckBox1_Click()

    Worksheets(“WebADI”).Range(“C19:Q272”).value = Worksheets(“Allocation”).Range(“K9:Y262”).value

    End Sub

    Private Sub CheckBox2_Click()

    Worksheets(“WebADI”).Range(“C274:Q536”).value = Worksheets(“Allocation”).Range(“K273:Y526”).value

    End Sub

  • Hi Jon:
    I’m not an expert programming with VBA. I need help with a code, here is what I need. I have data in a sheet from column C to DA, column C has a symbol and D has a value, E has symbol and F has value, and so on until column AD. Here I go, in each column my range is from 12 to 72. I need to count 36 values starting from the cell 72 up to 12 ignoring empty cells (To maintain 36 values.) then clear the data over the value number 26. Also clear the symbol to the left of the deleted values. This is what I have done I started with column C to eventually change it from column A to DA but is now doing what I want.

    Sub Data_60_To_36()
    Dim y As Long, cnt As Long, x As Long  x or future modification.
    For y = 12 To 72 Step 1
    ‘For x = 2 To 105 step 2  For future modification to be used to expand range.
    ‘ If not blank then count rows
    If Sheet2.Cells(y, “C”) “” Then
    ‘ count rows
    cnt = cnt + 1
    If cnt > 36 Then Exit For
    End If
    Next

    ‘ Clear the cells at the Top
    Sheet2.Range(“C12:C” & y).ClearContents

    End Sub

    Thanks Jon

  • Hi Jon,

    Great stuff – I have a large columned worksheet that I want to find and delete all “blank” cells – any help you can provide would be of great assistance – if you provide an example, I can work with to find the final answer, I’m willing to do the necessary work from there..

    Thanks!
    Dan Starzec

    • Hi Dan,
      Great question! I don’t have any specific tutorials on that yet. However, the Range.Clear or Range.ClearContents methods will delete the cells. ClearContents deletes the values/formulas, but leaves the formatting. I hope that helps. Thanks!

  • Hi Jon,

    I have 2 tabs in my worksheet. The 1st tab consists of Email template and a button to generate outlook email. The 2nd tab consists of multiple rows of data which are referenced by tab 1 for email purpose. Row 1 of tab 2 has fixed reference to tab 1 template.

    Now on clicking the email generate button picks the data in tab 1 referenced from row 1 of tab 2 and sends an email.

    Where I need your help is on a code that:
    Moves the rows up in tab 2 once email for row 1 is sent out. So that the tab 1 now references row 2 data of tab 2.
    Note: moving up the rows should be copy+paste and not cut+paste since it removes the references of row 1 to tab 1.

  • I would like to create a macros which will move me to the end of the list.
    Not only to find last row or column and see it in ms-box, but also to move physically to the end the active cursor.

    • Hi Kristine,
      You can use the Select method to select the last used cell. Depending on what method you use, the code could look something like the following.

      Cells(lRow, lCol).Select

      You would put that after the code to find the last row and column. I hope that helps. Thanks!

        • Hi Jon, Kristine

          I have the same senario as Kristine and I have tried to get the code to work for me. I am hitting a brick wall though and wondering if I have misunderstood something? Would it be possible to share with me your code string?

          Thanks
          Geoff

  • Useful article, thanks.

    When talking about cons of using SpecialCells, you say that it “finds the last used cell and NOT the last non-blank cell”.

    Could you please clarify? Is this connected to issue of Excel not refreshing the last cell reference until it has been saved, or is it a further point?

    • Great question David! The last used cell can be a cell that contains formatting, or that once contained a formula or value but has since been deleted. Excel won’t necessarily change the used range, even after you save the workbook, for these cases. Therefore, the last used cell can be different from the last non-blank cell.

      You can use this to your advantage too if you want to determine if there are cells after the last non-blank cell in the worksheet. I hope that helps. Let me know if you have any questions.

      Thanks!

      • Hi Jon,
        Very helpful, thanks.

        I found out that adding Activesheet.UsedRange before using SpecialCells(xlCellTypeLastCell) forces a refresh of the last cell as if it has been saved.

        However, I now see that where there may residual formatting, for example, this method of finding the last blank cell is not bullet proof.

        … back to Range.Find then!

        Thanks again.

        • Hi David,
          That’s a great tip with UsedRange! And have come to the same conclusion with Range.Find. It is not the prettiest, but is usually the most versatile and bullet proof. I just keep the FindLast function in my personal macro workbook and copy it to any project I’m working on. Thanks again!

  • Hi Jon,

    Just wondering how I could find a specific cell in a row of dates, then use this as a variable to reference this column as a starting point, (its not the end of data as there are dates in the future as well)?

    Thanks

    • Hi Pip,
      Great question! You can use the Range.Find method for that. The Find method can return a range object. Here is some code to get you started.

      Sub Range_Find_Method_Range()
      'Finds the first cell in range A1:Z1 that contains the date 12/20/2015
      
      Dim rFind As Range
          
          On Error Resume Next
          Set rFind = Range("A1:Z1").Find(What:="12/20/2015", _
                          After:=Range("A1"), _
                          LookAt:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByColumns, _
                          SearchDirection:=xlNext, _
                          MatchCase:=False).Cells
          On Error GoTo 0
          
          MsgBox "Found Date in: " & rFind.Address
      
      End Sub

      The macro above is similar to the macros in the article for the Range.Find method to find the last row or column. However, there are some key differences. You’ll notice that this macro uses the Set keyword “Set rFind = “.

      This is because the rFind variable is an object, a Range object. We need to use Set to set the object variable types.

      I also changed the What:= parameter to a date. You can change that to the date you are looking for. Or you can use a variable instead. Maybe you want to have an InputBox appear where the user inputs the date.

      The other change is the Range we are looking in. I specified Range(“A1:G1”), and the find method will look in all the Cells in that range. You can change that reference to fit your needs as well.

      I hope that helps. Let me know if you have any other questions. Thanks!

      • Hi Jon,

        Thats fantastic thanks so much and the explanation really helps with understanding the code.

        As another variant if I have a row that has “ACT” in it up until it gets to “FCT” how can I find the last item of “ACT” in the row.

        So I am wanting to find where my actuals end and forecast start.

        Thanks
        Pip

        • Hey Pip,
          In that case you can use the Range.Find method again. This time you can use Range.Find for the last row (code sample above), and just change the What parameter to “ACT”.

          Sub Range_Find_Method_Row()
          'Finds the last cell in column A where the cell value is ACT
          
          Dim lRow As Long
              
              On Error Resume Next
              lRow = Range("A:A").Find(What:="ACT", _
                              After:=Range("A1"), _
                              LookAt:=xlWhole, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
              On Error GoTo 0
              
              MsgBox "Last Row: " & lRow
          
          End Sub

          I also changed the range reference to Range(“A:A”). This will look in column A only. Since the Start parameter is A1 and the Direction is xlPrevious, the find will start in cell A1 and go backwards. That means it jumps to the last row in the worksheet then starts going up. It will stop when it finds the first cell that contains “ACT” and stop there.

          I also changed the LookAt parameter to xlWhole. This is the same as the “Match entire cell contents” in the Find window. You will probably want this option to find an exact match. In other words, you don’t want the find to stop if a cell below contains the word “Fact”.

          The Find method is a bit of a jigsaw puzzle with all the parameter options. The cool part is that once you understand how it works, it can be an extremely powerful tool. Kind of like the Hulk. Big, scary, and ugly, but also fast and powerful. I hope that helps. 🙂

  • Hi There,

    Will any of the above sintax help finding the last row of an actual table (rather than just a range) and then selecting a defined cell in the last row of that cell ?

    I have been searching for quite a long time and I can’t seem to make “Rows.Count” or Range.find work with ActiveSheet.ListObjects(“Table4”).

    Any pointers would be greatly appreciated.

    • Hi Ollie,

      Great question. The DataBodyRange property will return the data range of the Table. Here is the code.

      ActiveSheet.ListObjects(“Table4”).DataBodyRange.Rows.Count

      That will return the total number of rows in the Table.

      You can also use the Find method with DataBodyRange

      ActiveSheet.ListObjects(“Table4”).DataBodyRange.Find(“A”)

      That is just a simple example. Let me know if you have any questions. Thanks!

  • Hello Jon!!! Thanks for this post, and for the videos, they are very helpful!

    My question is simple: I´m using the code to get the last available row (range.end), and then I would need to copy all the cells above the last row into another sheet. This last cell will be different each time. Can you help me with the necessary code? Do you need more information?

    Thanks!!

    • Hi Martin,

      Glad to hear it helped. 🙂

      Once you have the last row you can reference in the Range property. So let’s say you want to always copy columns A to F, starting in row 2, but the last row changes. Here is the code.

      Range("A2:F" & lRow).Copy

      There are a lot of different ways to go about this (like anything in Excel/VBA), but this should help get you started. Let me know if you have any questions. Thanks!

  • Hi Jon, I have in the past used an Excel worksheet function called from within VBA,

    for example in the form

    Dim ans as Variant

    ans = Application.Worksheetfunction.Substitute(MyText, ” “, “”) (This wors)

    But if I try to use the Worksheet Function =Columns(MyTable) which incidentally works Ok when used on a Worksheet from within a Cell
    BUT
    WONT work in the form ans = application,worksheetfunction.Columns(MyTable)

    i.e the function doesn’t seem to be available from within VBA do you have any idea why ?

    Is there an addin needed ?

    Regards

    Fred

    • Hi Fred,
      Sorry to not get back to you sooner. I just saw your comment. The Columns worksheet function does not exist in VBA. However, you can use VBA instead.

      The following line of code will return the number of columns in the table/range.

      Range("MyTable").Columns.Count

      I hope that helps. Let me know if you have any questions. Thanks

  • I know the likelihood of getting a response on such an old thread is unlikely, but I am needing a way to use this on all sheets in a workbook, individually, and type “X” on the next row in column B.

    The following is working on each sheet, but I need it to loop through all sheets and find that sheets last row. I have been unsuccessful at making it accurate on each sheet individually.

    Dim iRow As Long
    Dim iCol As Long

    iRow = Cells.Find(What:=”*”, _
    After:=Range(“A1”), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row

    Cells(iRow + 1, 2).Select
    ActiveCell.FormulaR1C1 = “x”

    • Hi Faith,
      Great question! The code for the loop will look something like the following.

      Dim ws as worksheet

      For Each ws in Thisworkbook.Worksheets
      
         'Add your code for finding the last cell and adding the x
      
      Next ws

      That code will loop through all the sheets in the workbook and run the code you put between the For and Next lines. “Thisworkbook” references the workbook that the code is stored in. You could replace that with “ActiveWorkbook” to run the code on the workbook you currently have active.

      I have a free video training series on macros & VBA that discusses how to write a loop to loop through all sheets in the workbook.

      I hope that helps. Let me know if you have any questions.

  • Hi Zack and other gurus,

    i have a question regarding how to solve the issue when there is a blank sheet when we use the last cell find vba syntax. any suggestions for error handling? it is part of my syntax to return all the formula of a sheets. currently there is a bug if the the workbook contains a blank sheet. i do not know how to put an error handling code to it.

    thanks so much

    Jane

    • Hi Jane,

      There are few ways to go about it. You could use the the CountA worksheet function to first determine if the sheet is blank.

      Application.WorksheetFunction.CountA(ActiveSheet.Cells) = 0

      If this returns a TRUE value then the sheet is empty. Replace ActiveSheet with the reference to the sheet in your loop.

      Please let me know if you have any questions. Thanks!

  • I should add that I tried a simple

    MsgBox lastRow
    emptyRow = lastRow + 1
    MsgBox emptyRow

    Although the message box for lastRow returns the correct value, the message box for emptyRow shows a value of “1” and I am not sure why.

    I used the code from Part 2, which worked perfectly – thank you for the detailed explanation!

    Thank you,
    Lynn

  • Nice post Jon. Well stated and easy to follow. I didn’t download any of the files, but followed the post and watched the videos.

    The reason behind using Rows.Count and Columns.Count is because of the varying number of rows in different versions of Excel. There’s a lot of code out there which has “A65536” and “IV1” hard-coded in it because for a long time nobody ever thought the number of rows in Excel would ever increase. So to be forward and backwards compatible, using the Count is the most robust because it will adjust based on the [Excel] version its being run in.

    The most important point not mentioned is when using any of these methods you should always specify the worksheet. Whether you’re using the End or Find method, specify the worksheet. Here is an example of a simple usage of the End method of finding the last row in column A:

    Cells(Rows.Count, 1).End(xlUp).Row

    The problem with not specifying the worksheet is this code will always assume each range reference is based on the active sheet. So if you have multiple sheets with different amounts of data, it could cause havoc.
    Changing the above code to contain a specific sheet reference would be:

    ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    Note two thing: 1) I used the ActiveSheet object, and 2) each range object received the worksheet reference. On many occasions you’ll see someone reference the outermost range reference but not the others. Obviously you should change the ActiveSheet for the sheet reference you’re using.

    Something else to consider is the UsedRange object is normally used to determine the range used, and is often used when trying to identify any Range on a worksheet. It has some deficiencies however, such as not always being current and being unable to ignore formatting. Using the Find method ignores rows hidden with AutoFilter. To get the real last used row, ensure that all rows are visible by turning off any auto filters. The Find method is not affected by manually hidden rows and columns.

    In regards to the SpecialCells method, it can be nice and flexible, but it will throw an error if Nothing is returned, so there would need to be error handling along with it. What I do when using this method is error handle it then check for the existence of the variable I used with it for being Nothing.

    Really enjoyed the read!

    • Thanks for the comment Zack! I really appreciate your input and knowledge. You are a VBA master my friend! 🙂

      Your explanation on why to use Rows.Count is excellent! And you’re absolutely right about specifying the sheet. I will add that to the example, and also add how it can be simplified with the With statement. For example, we could make the following line of code a little more efficient by using a With statement.

      ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

      Example using a With Statement:

      With ActiveSheet
      .Cells(.Rows.Count, 1).End(xlUp).Row
      End With

      This post was originally a lot longer. I cut some things out to try and simplify the explanation, but you bring up some really important points. And simplification can often lead to errors. I might break it up into 3 separate posts to make it a little more digestible.

      Your other point about Filters and the Find Method is also really important. I forgot to mention that.

      Question for you. What method or technique would you use to find the last cell on a sheet that contains a blank Table? If a blank table is inserted on a sheet, UsedRange or SpecialCells doesn’t seem to find the last cell. The only thing I can think of is to loop through the ListObjects (tables) and return the range address property.

      Thanks again Zack! I really appreciate your help! 🙂

      • That definitely [Tables] presents another layer of complexity. For me, because of their inherent dynamic nature, I don’t feel the need to have a function to find the last row of a Table because that range is basically always known. There are really only a couple of situations where using the ListRows object wouldn’t give you the proper Range object return.

        Using the Find method (generally what I prefer) will not have the same End range, because the entire Table range is encompassed in the UsedRange object, but the Find method will only look for data.

        I have all of my Table code in a class module, so I can keep everything portable and modular. Most of my functions are for finding specific rows/columns in a specific Table, but finding the last Table row on a sheet is a bit different. The procedure below doesn’t look at any other range on a sheet except the Tables, but is what I use…

        Function LastTableRow(Optional ByVal TableSheet As Worksheet) As Long

        ‘ Finds the last row of all Tables on a specified worksheet. If omitted the active sheet is used.

        ‘ Author: Zack Barresse

        ‘ Syntax: LastTableRow([TableSheet])

        ‘ Parameters: TableSheet. ListObject. Optional. The Table to insert a new row in.

        Dim Table As ListObject
        Dim LastRow As Long

        ‘ Get passed sheet object
        If TableSheet Is Nothing Then
        If ActiveSheet Is Nothing Then Exit Function
        Set TableSheet = ActiveSheet
        End If

        ‘ Iterate all Tables on specified sheet
        For Each Table In TableSheet.ListObjects
        If Table.DataBodyRange Is Nothing Then
        LastRow = WorksheetFunction.Max(Table.InsertRowRange.Row + 1, LastRow)
        Else
        LastRow = WorksheetFunction.Max(Table.ListRows(Table.ListRows.Count).Range.Row, LastRow)
        End If
        If Table.ShowTotals Then LastRow = LastRow + 1
        Next Table

        ‘ Assign value
        LastTableRow = LastRow

        End Function

        Honestly I don’t have any routines which mix/match finding last row/column/cell on a worksheet versus Tables. I have a similar routine that you posted above for LastCell, somewhat based on Ron de Bruin’s as well. Maybe it’s time to incorporate Tables into that function as well. 🙂

        • Thanks Zack! Incorporating Tables in the LastCell function is a great idea. They are kind of mini-worksheets on their own.

          The LastTableRow function is awesome! It resolves my issue of finding the last used row on a sheet with a blank table. Here is a screenshot of the issue.

          LastTableRow Function vs Ctrl End on Blank Table.png

          Is this function in your book? I highly recommend checking out Zack’s Excel Tables book if you want to learn more about Excel Tables.

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly