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

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

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 = ThisWorkbook.Worksheets("Sheet1")

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

Below are some other great ways to find the last row or column that were shared by some of **The Spreadsheet Guru** readers. Thank you to everyone who contributed!

Sub FindingLastRow_Alternatives()

Dim sht As Worksheet

Dim LastColumn As Long

Set sht = ThisWorkbook.Worksheets(Sheet1.Name)

'Provided by Bob U.

LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

End Sub

Taking it one step further, 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

**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 Facebook, Twitter, 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!*