5 Different Ways to Find The Last Row or Last Column Using VBA

5 Different Ways to Find The Last Row or Last Column Using VBA In Microsoft Excel

Making Your VBA Code Dynamic

Early on when I was first beginning to write VBA macro code, I always needed to go back into the code and modify range references.  I had created a bunch of macros to cleanup and perform analysis on raw data exported from database warehouses and the data never had the same amount of rows from one data-pull to the other.  My coding skills dramatically changed the day I realized my VBA code could be dynamic and automatically determine the size of my raw data once executed.  I soon came to realize the goal of coding a macro:  to write it once and never touch it again.

Variability is also the greatest challenge for any computer coder as you have to think of every possible change that could occur in the future.  I have found that writing VBA code that can automatically resize itself is one of the greatest things missing from most average macro users.  Below I have compiled a list of all the major ways I have 

Use Caution! 

Keep in mind, some of these methods may not give you the desired row or column number if you are not setting them up properly or using a well-formatted data sheet.  What I mean by a "well-formatted data sheet", is a worksheet with data that starts in cell A1 and does not have any blank rows or columns in the middle of the data.  The below figure illustrates the difference.

An Example of a Poorly-Formatted Data Set

An Example of a Poorly-Formatted Data Set

An Example of a Well-Formatted Data Set

An Example of a Well-Formatted Data Set

In a data set starting in Row 4, you may need to add or subtract a numerical value depending on the method you use.  If you are going to be coding for a data set that has blank rows or columns within it, always be sure to test out your code to make sure it is calculating properly.

Ways To Find The Last Row

Sub FindingLastRow()
'PURPOSE: Different ways to find the last row number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

'Using Find Function (Provided by Bob Ulmas)
  LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

'Using SpecialCells Function
  LastRow = sht.Cells.SpecialCells(xlCellTypeLastCell).Row

'Ctrl + Shift + End
  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row

'Using Table Range
  LastRow = sht.ListObjects("Table1").Range.Rows.Count

'Using Named Range
  LastRow = sht.Range("MyNamedRange").Rows.Count

'Ctrl + Shift + Down (Range should be first cell in data set)
  LastRow = sht.Range("A1").CurrentRegion.Rows.Count

End Sub

How To Expand Your Range To The Last Row

Dim DataRange As Range

Set DataRange = Range("A1:M" & LastRow)

Ways To Find The Last Column

Sub FindingLastColumn()

'PURPOSE: Different ways to find the last column number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastColumn As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

'Ctrl + Shift + End
  LastColumn = sht.Cells(7, sht.Columns.Count).End(xlToLeft).Column

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column

'Using Table Range
  LastColumn = sht.ListObjects("Table1").Range.Columns.Count

'Using Named Range
  LastColumn = sht.Range("MyNamedRange").Columns.Count

'Ctrl + Shift + Right (Range should be first cell in data set)
  LastColumn = sht.Range("A1").CurrentRegion.Columns.Count

End Sub

How To Expand Your Range To The Last Column

Dim DataRange As Range

Set DataRange = Range(Cells(1, 1), Cells(100, LastColumn))

What Can I Do With A LastRow Or LastColumn Variable?

There are a ton of things you can do with calculating the last row or last column of a data set.  Examples could be:

  • Resizing a Pivot Table range
  • Looping through cells in a column
  • Deleting only the raw data range

There are many, many more examples of this and I'm am sure you can think of a few examples yourself.  Let me know in the comments section below how you use resizing a range in your macro code!  Also, if you can think of any other ways to use VBA code to find the last row or last column, post your coding method in the comments section so we can improve the current list.  I look forward to reading about your experiences.

UPDATE: Alternative Methods Provided By You!

Tim provided the inspiration for a function that can return either the last row or column number through a user defined function for a given worksheet.

An example of how you could call this function to return the last row on the active worksheet would be written as: x = LastRowColumn(ActiveSheet, "Row")

Function LastRowColumn(sht As Worksheet, RowColumn As String) As Long
'PURPOSE: Function To Return the Last Row Or Column Number In the Active Spreadsheet
'INPUT: "R" or "C" to determine which direction to search

Dim rc As Long

Select Case LCase(Left(RowColumn, 1)) 'If they put in 'row' or column instead of 'r' or 'c'.
  Case "c"
    LastRowColumn = sht.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious).Column
  Case "r"
    LastRowColumn = sht.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
  Case Else
    LastRowColumn = 1
End Select

End Function


A brand new Excel add-in that will automate creating PowerPoint slides from your Excel data! Click the image to learn more! #FreeTrial

A brand new Excel add-in that will automate creating PowerPoint slides from your Excel data! Click the image to learn more! #FreeTrial

Share This Post!

Did you find this post helpful? Do you want to support this blog because you're just that awesome?!  By sharing this post on FacebookTwitter, or Google+ you are not only providing others with great information, you are creating exposure for The Spreadsheet Guru website.  Growing this community is my number one goal as I have found learning to increase exponentially when lots of people are contributing to the conversation.  Learning is the whole reason why this blog exists!  If you want to spread the word just click on the Share button right below this paragraph (next to the Like button -- I enjoy "likes" too!) or on the Floating Share Bar to the left and select your preferred social medium.  Thank you so much for reading and I hope I can continue to provide you with great content in the future!  Cheers!