VBA Tutorial: Find the Last Row, Column, or Cell in Excel
104

VBA Tutorial: Find the Last Row, Column, or Cell on a Sheet

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! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 104 comments
www.bestgiantladder.com - August 25, 2017

Everything is very open with a clear explanation of the issues.
It was really informative. Your website is very helpful.

Thanks for sharing!

Reply
Christian - August 17, 2017

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!

Reply
Lerry - July 12, 2017

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

Reply
Suraj - July 10, 2017

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

Reply
Pascale - July 4, 2017

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

Reply
Raghava - June 29, 2017

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

Reply
Raghava - June 29, 2017

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

Reply
Manoj - June 14, 2017

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

Reply
James - May 24, 2017

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

Reply
Anupam - May 16, 2017

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?

Reply
Aaiyah - March 15, 2017

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

Reply
Tom Stacy - February 22, 2017

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

Reply
Jean - February 7, 2017

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

Reply
    Jon Acampora - February 15, 2017

    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.

    Reply
Sachin Kelkar - January 24, 2017

thanks for this very good article

Reply
shweta - January 6, 2017

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?

Reply
    RQ - February 3, 2017

    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

    Reply
Abdul - January 2, 2017

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

Reply
Vilius - December 28, 2016

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?

Reply
    Jon Acampora - January 4, 2017

    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.

    Reply
      Gareth - January 6, 2017

      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

      Reply
        Jon Acampora - January 13, 2017

        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.

        Reply
Soren - December 17, 2016

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?

Reply
James - December 14, 2016

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

Reply
    Jon Acampora - December 19, 2016

    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!

    Reply
tulip - December 12, 2016

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.

Reply
Bernard - November 30, 2016

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

Reply
Nousiba - November 1, 2016

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

Reply
Linda - October 26, 2016

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?

Reply
    Jon Acampora - October 31, 2016

    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.

    Reply
Don - October 18, 2016

Hi Jon,

I found the solution…using the Max Function did what I needed…..

Don

Reply
    Jon Acampora - October 31, 2016

    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.

    Reply
Don - October 18, 2016

Hi Jon,

Is there a way to find the last used integer in a column with mixed data in it?

Don

Reply
Lloyd - October 16, 2016

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

Reply
    Jon Acampora - October 22, 2016

    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!

    Reply
njan - October 7, 2016

hello can just provide the excel sheet that can select last used raw in a column ignoring blanks

Reply
Gerard - August 8, 2016

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

Reply
    Jon Acampora - August 8, 2016

    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!

    Reply
rick - August 3, 2016

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

Reply
    Jon Acampora - August 4, 2016

    Hi Rick,
    You could use the Max function for find the latest date. In VBA you use the following:

    WorksheetFunction.Max(Sheet1.Range("A1:A10"))

    That line of code would return the Maximum value from the specified range. I hope that helps.

    Reply
      rick - August 4, 2016

      Thanks for your reply and suggestion.
      But i try this code yet it’s cant run as expected.
      Can i know more specific?
      I try to input 123 in userform and used to find in the worksheet
      (find the latest 123 )

      thanks
      rick

      Reply
      Balaji - November 18, 2016

      How to find out the start of the cell value and end of the same value in a column. Ex A1=apple, A2=Apple, A3= Apple. I need result as the Start value is “A1” and End value is “A3”.

      Reply
John D. - July 21, 2016

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))

Reply
    Jon Acampora - July 31, 2016

    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.

    Reply
Dinesh K Takyar - July 9, 2016

Very well explained. Thank you!

Reply
stephen prince - June 29, 2016

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

Reply
Chris - June 24, 2016

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.

Reply
Richard Langstaff - June 22, 2016

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

Reply
    Jon Acampora - June 23, 2016

    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!

    Reply
Roy - June 17, 2016

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

Reply
Wilfredo Charriez - June 15, 2016

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

Reply
Dan - June 7, 2016

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

Reply
    Jon Acampora - June 8, 2016

    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!

    Reply
Pawan - May 31, 2016

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.

Reply
Kristine - May 27, 2016

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.

Reply
    Jon Acampora - May 30, 2016

    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!

    Reply
      Kristine - May 30, 2016

      Thank you!
      It will help me to navigate in my data table.
      🙂

      Reply
        Geoff - June 24, 2016

        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

        Reply
David Orr - April 29, 2016

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?

Reply
    Jon Acampora - May 2, 2016

    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!

    Reply
      David Orr - May 2, 2016

      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.

      Reply
        Jon Acampora - May 9, 2016

        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!

        Reply
Pip - April 8, 2016

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

Reply
    Jon Acampora - April 8, 2016

    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!

    Reply
      Pip - April 8, 2016

      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

      Reply
        Jon Acampora - April 8, 2016

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

        Reply
Ollie Johnson - March 8, 2016

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.

Reply
    Jon Acampora - March 9, 2016

    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!

    Reply
    Ollie - March 10, 2016

    Thank you so much !! That was exactly what I was looking for.

    Reply
Martin Barbaglia - March 2, 2016

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!!

Reply
    Jon Acampora - March 3, 2016

    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!

    Reply
Fred - February 14, 2016

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

Reply
    Jon Acampora - March 16, 2016

    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

    Reply
Faith M - January 28, 2016

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”

Reply
    Jon Acampora - February 1, 2016

    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.

    Reply
jane - November 13, 2015

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

Reply
    Jon Acampora - November 15, 2015

    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!

    Reply
Lynn Benjamin - August 8, 2015

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

Reply
    Jon Acampora - August 9, 2015

    Hi Lynn,
    Would you mind posting your entire macro here so I can see what’s going on. You can also just send your file to me if you’d like. jon@excelcampus.com

    Thanks!

    Reply
    Zack Barresse - August 10, 2015

    Are you in run-time when you view the “1” value, when your mouse hovers over the variable? Or is this in the locals or watch window? We would definitely need to see your code though, otherwise it’s a crap shoot. It *could* be a mis-declared variable though.

    Reply
Lynn Benjamin - August 8, 2015

Once I used Part 2 to find lastRow, how do I assign emptyRow as the very next row?

Thank you,
Lynn

Reply
How to find the last Table row on a sheet | EXCEL TABLES - June 3, 2015

[…] was a really good blog post by Jon Acampora over at ExcelCampus with a VBA tutorial on finding the last row, column, or cell on […]

Reply
Zack Barresse - May 15, 2015

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!

Reply
    Jon Acampora - May 16, 2015

    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! 🙂

    Reply
      Zack Barresse - June 1, 2015

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

      Reply
        Jon Acampora - June 3, 2015

        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.

        Reply

Leave a Reply: