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

http://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba

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