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

Watch on YouTube & Subscribe to our Channel

Video best viewed in full screen HD

Download the Excel file that contains the code:

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

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

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

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

150 comments

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

  • hi good evening
    I am trying to get some advice on a task that I’m trying to complete
    I have a excel sheet which contains columns from A to J
    What i want to do is cut the information under F-J
    And place them below the data that is already in A-E
    So I’m thinking there has to be some find list row first them paste to last row
    Can you assist

  • This is just perfect but doesn’t work with VSTO :'(

    Can you help to transpose this VBA code to VSTO ?

    THANK YOU IN ADVANCE!

  • Hi can you please tell me that is the error in the below syntex

    Sub logic_17a()
    ThisWorkbook.Activate
    Worksheets(“logic17a”).Select
    LRow = Cells(ActiveSheet.Rows.Count, 1).End(xlsm).Row
    x = Sum(Range(Cells(2, 4).Cells(LR, 4)))
    Cells(LRow + 1, 4).Value = x
    End Sub

  • This is fine for the last used cell in a row or column; however, another common need is to find the last used cell in a range of a column or row. .e.g. what is the last used cell in the range(“A5:A10”). How does one do that?

    • LastCell = Range(“A5”, “A10”).SpecialCells(xlCellTypeConstants).End(xlDown).Address
      MsgBox (LastCell )

      if you want only row number, change .Address with .Row

  • Very nice example.
    I wanted to find the last active row in a sheet so I added

    lastcell = Range(“A1”).SpecialCells(xlCellTypeLastCell).Address

    lastrow = Range(lastcell).Row

    worked like a charm

  • function for find last row, more faster than ‘.find’

    P.S : Sorry for my bad English but i am french so…

    Function LastRow(Optional wSheet As Worksheet) As Long

    ‘ cocorico

    Dim i, NbCol, EndRow As Long

    LastRow = 1

    If Range(“B1”).Value = “” Then
    NbCol = 1
    Else
    NbCol = Range(“A1”).End(xlToRight).Column
    End If

    EndRow = Cells(1, NbCol + 1).End(xlDown).Row

    For i = 1 To NbCol

    If Cells(EndRow, i).End(xlUp).Row > LastRow Then LastRow = Cells(EndRow, i).End(xlUp).Row

    Next i

    End Function

    • how to use this function.
      please send sample code
      i am getting type mismatch error

      msgbox lastrow(“sheet1”)

      or
      msgbox lastrow(sheet1)

    • This function assumes a lot.

      It assumes that the header or data starts in row 1.
      It assumes that there is no gap in headers in row 1. (What happens if C1 is Blank, but F1 -Z1 are not?)

      Why not use the same method used for finding the last row to find the last column? (Because it would take just as long, or longer then the Range.Find method)

      If you have an understanding of the data that you are expecting, there is no need to check all the columns, just check the mandatory column(s) that you will be using to sort/filter the data.

      Either way, if you have Filtered data, none of the methods will work properly.

  • Hello Everyone,

    As now I am working on VBA code I used Cells(Rows.Count, 2).End(xlUp).Row but it show me wrong number because my excel sheet having Table style so it showing last cell of this table even it’s blank. Please help me to search non blank cell even excel having Table style.

    Thanks.

  • Hi Jon, Many thanks for this article. It has helped me greatly in preparing for my interview. Much appreciated.
    Thanks,
    Kaz

  • Hi Jon!
    Your tips are always great, specially when it comes about shortcuts! Thank you for all!

    Regarding finding last row, I typically record a Macro by going to the cell A1, and pressing CTRL+SHIFT+END. It has served me well so far. The VBA code stays like this:

    Range(“A1”).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

    Seems pretty simple and workable.
    Do you find any disadvantages or possible errors on this method?

  • What is the proper way to use these functions to when you have multiple workbooks and worksheets? We just updated from 2013 to 2016 and a lot of our projects broke due to these functions accessing different workbooks under one version than the other.

  • Hi Jon,

    What would be the best way to find AND select the last last row in a range? I am attempting to enter a formula in the first row of a range and copy it down to the last row in the range?

    Thanks!

    • Hi James,

      You can use a variable to store the value of the last row, then use in the range reference. There are many ways to do this, but something like the following will work.

      Range("B1:B" & lRow).FillDown

      This assumes the lRow variable contains the number of the last row, which will be set in a line a code above this. It also assumes that your formula is in cell B1 and you want to copy it down.

      I have a bonus video at the bottom of this page that explains how to copy and paste below the last used row. Some of the techniques in that video might help you as well.

  • Hi,

    I’m new to VBA , please someone tell me where should i start to learn VBA , is there free sites i can go through?

    Thnak you

  • ‘To solve the problem with the blank lines and columns we can use the ‘following code.
    ‘This code deletes blank columns and rows, from the end, to clear, release, ‘sheet and memory
    ‘Also, if excel had many empty rows and columns, after implementing the code, ‘it is getting faster !

    Sub rEalLastColumn()
    Dim lAstCol As Long
    Dim lAstCellUsedRange As Range
    Dim wS As Worksheet
    Dim lEtterOfColumn As String
    Dim lEtterOfColumnFirstTime As String
    Dim ActiveCellAddress As String
    Dim x As Integer
    Dim lAstRow As Integer

    ActiveCellAddress = ActiveCell.Address
    Set wS = ActiveSheet
    Set lAstCellUsedRange = wS.UsedRange.Cells(wS.UsedRange.Rows.Count, wS.UsedRange.Columns.Count)
    lAstCol = lAstCellUsedRange.Column
    lEtterOfColumnFirstTime = Split(Cells(1, lAstCol).Address, “$”)(1)
    For x = lAstCol To 1 Step -1
    lEtterOfColumn = Split(Cells(1, x).Address, “$”)(1)
    lAstRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, lEtterOfColumn).End(xlUp).Row
    If lAstRow 0 Then
    lAstCol = x
    Exit For
    End If
    ActiveSheet.Columns(lEtterOfColumn).Delete
    Next
    ‘Range(ActiveCellAddress).Select
    ‘MsgBox (lAstCol) & ” ” & lEtterOfColumn & ” Last Column ”

    End Sub

    Sub rEalLastRowAndDeleteEmptyRowsToClearSheetAndMemory()
    Dim lAstCol As Long
    Dim lAstCellUsedRange As Range
    Dim wS As Worksheet
    Dim lEtterOfColumn As String
    Dim lEtterOfColumnFirstTime As String
    Dim ActiveCellAddress As String
    Dim x As Integer
    Dim lAstRow As Integer
    Dim lEtterOfRow As String

    ActiveCellAddress = ActiveCell.Address
    Set wS = ActiveSheet
    Set lAstCellUsedRange = wS.UsedRange.Cells(wS.UsedRange.Rows.Count, wS.UsedRange.Columns.Count)

    lAstCol = lAstCellUsedRange.Column
    lAstRow = lAstCellUsedRange.Row

    lEtterOfColumnFirstTime = Split(Cells(1, lAstCol).Address, “$”)(1)

    For x = lAstRow To 1 Step -1
    lAstCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
    If lAstCol 0 Then
    lAstRow = x
    Exit For
    End If
    ActiveSheet.Rows(lAstRow).Delete
    Next
    ‘Range(ActiveCellAddress).Select
    ‘MsgBox (lAstRow) & ” ” & ” Last Row”

    End Sub

    • TThe above code is not the one I published.

      I do not understand how it changed.
      The follower codes is the right onehis is not my codes..
      My codes are that following

      ‘Kostas Moschidis – February 19, 2018
      ‘‘To solve the problem with the blank lines and columns we can use the ‘following code.
      ‘‘This code deletes blank columns and rows, from the end, to clear, release, ‘sheet and memory
      ‘‘Also, if excel had many empty rows and columns, after implementing the code, ‘it is getting faster !

      Sub rEal_LastCol_AndDeleteEmptyColumnsToClearSheetAndMemory()
      Dim lAstCol As Long
      Dim lAstCellUsedRange As Range
      Dim wS As Worksheet
      Dim lEtterOfColumn As String
      Dim lEtterOfColumnFirstTime As String
      Dim aCtiveCellAddress As String
      Dim x As Integer
      Dim lAstRow As Integer

      aCtiveCellAddress = ActiveCell.Address
      Set wS = ActiveSheet
      Set lAstCellUsedRange = wS.UsedRange.Cells(wS.UsedRange.Rows.Count, wS.UsedRange.Columns.Count)
      lAstCol = lAstCellUsedRange.Column
      lEtterOfColumnFirstTime = Split(Cells(1, lAstCol).Address, “$”)(1)
      For x = lAstCol To 1 Step -1
      lEtterOfColumn = Split(Cells(1, x).Address, “$”)(1)
      lAstRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, lEtterOfColumn).End(xlUp).Row
      If lAstRow 0 Then
      lAstCol = x
      Exit For
      End If
      ActiveSheet.Columns(lEtterOfColumn).Delete
      Next
      MsgBox (lAstCol) & “=” & ” lAstCol”
      End Sub

      Sub rEal_LastRow_AndDeleteEmptyRowsToClearSheetAndMemory()
      Dim lAstCol As Long
      Dim lAstCellUsedRange As Range
      Dim wS As Worksheet
      Dim lEtterOfColumn As String
      Dim lEtterOfColumnFirstTime As String
      Dim aCtiveCellAddress As String
      Dim x As Integer
      Dim lAstRow As Integer
      Dim lEtterOfRow As String

      aCtiveCellAddress = ActiveCell.Address
      Set wS = ActiveSheet
      Set lAstCellUsedRange = wS.UsedRange.Cells(wS.UsedRange.Rows.Count, wS.UsedRange.Columns.Count)

      lAstCol = lAstCellUsedRange.Column
      lAstRow = lAstCellUsedRange.Row

      lEtterOfColumnFirstTime = Split(Cells(1, lAstCol).Address, “$”)(1)

      For x = lAstRow To 1 Step -1
      lAstCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
      If lAstCol 0 Then
      lAstRow = x
      Exit For
      End If
      ActiveSheet.Rows(lAstRow).Delete
      Next
      MsgBox (lAstRow) & “=” & ” Last Row”
      End Sub

      • finale the problem is this

        If lAstCol0 Then —- If lAstCol not equal 0 Then

        because the page can not display the unequal symbol

  • I am trying to use VBA to find first blank cell in a specific COLUMN and start looking at a specific ROW of a worksheet.
    My code can find blank in column(5) but fails because i have a merged cell range and formula in ROW 1
    I need to .PasteSpecial xlPasetValues at the point found above

  • There is a tiny difference between the first and the second method: to get the same result in the second case you need to activate the sheet to be seached:

    Sheet2.Activate

    When I don’t use the above line I get quite a wrong result using Range.Find 😉

  • In addition to the “Cons of Range.SpecialCells” section:
    • Doesn’t work with protected sheets (causes the Run-time error 1004)

  • How do you find the last cell in a print range, whether used or not used? What if a worksheet has multiple print ranges? How do I go from the first to the next.

    I would like to navigate to the last cell in the print range and adjust the row height and column width to fill the page to the margins. Forcing the worksheet to fit to print to one page is not what I want.

  • Hello, I used the Range.Find method to find the number of the last column in a row, this number being used to display the a numerical value showing how many columns the data encompasses. It works fine normally, but when I filter is applied to the rows the number of columns drops, even if all columns are occupied. I would like for it to display the same number regardless of what filters are applied, is there a way to do this?

    • Thanks a bunch for sharing this with all folks you really realize what you are speaking about! Bookmarked. Please also visit my site =). We could have a hyperlink trade agreement among us

  • Hi Jon,

    Just thought I would share this with you and other readers (see sub below). I’ve used your Cells.Find() method with great success, but as you mentioned, it is a lot to type out and set up. It dawned on me to collapse it into a single line for each by using the “,” argument method vs. the “Argument:=” method. As you mentioned in your article, you can ignore the last two, as MatchByte is not relevant except for obscure language characters and unless you are finding cell or font formats, then SearchFormat can be ignored. At first, I thought I would need two comma placeholders at the end of the argument list (for MatchByte and SearchFormat), but I discovered it works without them.. probably because they are in succession and at the end of the list. Defining the lRow and lCol in this way makes it pretty easy to write on the fly and does not vertically stack up your code too much. Once you write it out a few times, it is pretty each to remember and you need change only two items to go from row to column. As a reminder, I commented the reference to the nine Arguments, just as a ticker to the names and the sequence in which they must appear if using only commas. Of course, there is nothing wrong with the more stacked “Argument:=” method.. just more bulk and so more cumbersome and or intimidating to use. Putting it all in one line makes it not that much more cumbersome than typing say: lRow = Cells(Row.Count, 1).End(xlUp).Row, but you get the benefit of the returned lRow being for the entire worksheet vs. just one column of the worksheet. Anyway, I thought I would share this different perspective. I hope others find it helpful. Thanks for the great tips and the inspiration to look at things in different ways. Thumbs up!

    Cheers!

    Wayne Edmondson

    Sub Find_Last_Used_Row_And_Column()

    Dim lRow As Long
    Dim lCol As Long

    ‘The 9 Find arguments are: What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat
    ‘Below, I’ve ignored unneeded arguments #8 and #9 which are: MatchByte and SearchFormat

    lRow = Cells.Find(“*”, Range(“A1”), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    lCol = Cells.Find(“*”, Range(“A1”), xlFormulas, xlPart, xlByColumns, xlPrevious, False).Column

    MsgBox “Last Row: ” & lRow & vbCrLf & vbCrLf & “Last Column: ” & lCol & vbCrLf & vbCrLf & “Cell address: ” & Cells(lRow, lCol).Address

    End Sub

  • hi, i’m having a little problem can somebody help,

    this is the code vba

    Dim lrow As Long

    lrow = Sheet1.Cells(Rows.Count, “A”).End(xlUp).Row

    If Sheets(“Feuil1”).Range(“a2”) = “Canada” Then

    Range(“b2”).Value = (“CAN”)

    Else
    Range(“b2”).Value = (“INT”)

    End If

    End Sub

    it doesn’t scroll down the if statement but it works for only cell b2 but not for b3.b4.b5.etc…

    thank you!

    • It appears you are referencing two different sheets to find the last row. it is best to reference one sheet.

  • These articles are great, they are of help understanding the different approaches to address the need to find information.
    Thanks,

  • Hi, thanks for the article.
    I wanted to ask that if I had to get the value of the last non-blank cell and store it to a variable then how can I do it? That means I just want the value that the cell contains.

  • Hi,

    On my Dashboard (main page) I would like to show the last value in a specific column in another worksheet…
    Are you able to help me somehow?

    Have a nice day

  • Function Last_Row(ByVal col As Variant) As Long
    ‘ Returns number of last non-blank row in that column
    ‘ Corrects for program that randomly adds an ASCII 26 [EOF]
    ‘ in Col A way below the actual last row

    Dim LR As Long
    LR = Cells(rows.Count, col).End(xlUp).row
    If Cells(LR, “A”) = Chr(26) Then
    Cells(LR, “A”) = “”
    LR = Cells(rows.Count, col).End(xlUp).row
    End If
    Last_Row = LR
    End Function

  • How to find the last cell of the value and select the range like an example I have a data contains A1: A10, I have to fill the value in B1: B10 (it’s empty cells). so I need to find out the last row of A column and using offset to fill the Range B1:B10 ( this is an example) i have to given the dynamic range. so can you help me to fix this issue

  • Hi, I have a problem
    I want to hide them after finding the last row and column

    And the following code doesn’t work:

    Rows(“lRow:1048576”).Hidden = True

    Someone can solve my problem? please…

  • Thanks for the code and explanation!

    Can you tell me how to find the last cell in a column and copy the range from the first cell in the column to the last non-blank cell?

  • Thank U.
    How to find the Last row, in between there is an blank row.

    Example: Range(“A”), followed by i have 55 entries. Row 51 and 28 are blank, then how can i find the last row dynamically.

  • I have a preferred method of my own, similar to range.find but easier to use:

    LastRow = Range(“A1”).CurrentRegion.Rows.Count

    Give it a try 🙂

    • Really simple and works. However if you have missing data(whole line or column) then it won’t find the last location(unlike the articles code). Nevertheless, if you are sure you got data this line works great, Thanks.

  • Hi Jon,

    How do I select a table say for example having data from A15:AN15 having 100 rows with merged cells. I basically want to copy and paste this data as it is over sheet 2?

  • Please note the SpecialCells method ignores hidden rows and columns. If your last rows or columns are hidden, perhaps due to a filter, you won’t get the real last cell, only the last VISIBLE cell. (I’m now a convert to the .Find method – thank you.)

  • I’m a little late, but if someone finds my comment…

    I can call the last column/row with cells that are not empty.

    It’s a combination of ‘.SpecialCells’ and ‘.End’, using different functions, so that YOU can call them whenever you want.

    This way YOU only need to copy and paste them once. 😀

    You can check below:

    ””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””’
    Sub test()
    Dim r As range
    MsgBox (“Last Cell Address: ” & lasCell.Address)
    MsgBox (“Last row with data: ” & lasRow)
    MsgBox (“Last Column with data: ” & lasCol)
    End Sub
    ””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””’
    ‘GET LAST CELL
    Function lasCell() As range
    Set lasCell = Cells(lasRow, lasCol)
    End Function
    ””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””’
    ‘GET LAST ROW
    Function lasRow() As Long
    Dim XlasCell As range
    Set XlasCell = range(“A1”).SpecialCells(xlLastCell).Offset(1, 1)
    For i = XlasCell.Row To 2 Step -1
    If Cells(i – 1, XlasCell.Column).End(xlToLeft) “” Then
    lasRow = i – 1
    Exit For
    End If
    Next i
    End Function
    ””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””’
    ‘GET LAST COLUMN
    Function lasCol() As Long
    Dim XlasCell As range
    Set XlasCell = range(“A1”).SpecialCells(xlLastCell).Offset(1, 1)
    For i = XlasCell.Column To 2 Step -1
    If Cells(XlasCell.Row, i – 1).End(xlUp) “” Then
    lasCol = i – 1
    Exit For
    End If
    Next i
    End Function
    ””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””’

    lasCell Function ⇒ gives the bottomMost rightMost cell of the sheet’s “actual” used range

    lasRow Function ⇒ gives the LAST ROW of the sheet’s “actual” used range

    lasCol Function ⇒ gives the LAST COLUMN of the sheet’s “actual” used range

    • Oh, I now see that the last row/column won’t appear if hidden with the xlastcell method.
      seems like .find is the way

    • Hello Filipe. Thanks a lot for the code. I’m trying for a lot of time to find the last column and add a new one. However, the code you shared is showing an error in the below part.

      For i = XlasCell.Column To 2 Step -1
      If Cells(XlasCell.Row, i – 1).End(xlUp) “” Then
      lasCol = i – 1

      I think the “i” needs to be defined here.. but I’m not sure how.

      Could you please let me know where did I went wrong? Thanks a lot for your help.

  • Is there a way to find the first blank cell of a column and then count the cells above it (minus the header)?

    I’ve been playing with various versions of the following, but, other than at one point getting “=Count(P2:End(xlDown))” in that last cell, I haven’t had much success.

    As long as I have the second line in place, the first line does what I want it to.

    Range(“P1”).End(xlDown).Offset(1, 0).Select

    Value = “=Count(P2:End(xlDown))”

    In case it matters, here’s the whole macro:

    Sub Refund2()

    ‘ Refund2 Macro


    Columns(“A:F”).Select
    Selection.EntireColumn.Hidden = True
    Columns(“H:K”).Select
    Selection.EntireColumn.Hidden = True
    Columns(“M:M”).Select
    Selection.EntireColumn.Hidden = True
    Columns(“A:P”).Select
    Range(“P1”).Activate
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    With Selection.Font
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    End With
    Range(“P1”).End(xlDown).Offset(1, 0).Select
    Value = “=Count(P2:End(xlDown))”

    End Sub

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter