04 Dec Navigating Your Spreadsheet Using VBA
Howdee! When writing VBA code that interacts directly with data on a spreadsheet in Excel, being able to accurately define the size of your data set and iterate through rows and columns is crucial to being sure that your process runs effectively. Finding the last row using VBA, or last column, of a data set can be useful for knowing how many times your loop needs to run. Being able to define the size and location of a data set is important for everything from copying and pasting data, to organizing data for pivot tables. In this article, we will explore a handful of ways to do these things and point out some pitfalls to watch out for. With that in mind, let’s dive in!
Finding the Last Used Row or Last Used Column in Excel VBA
It’s extremely common to find the last row and use a for loop to iterate over the data set. Having said that, there are several ways to find the last row using VBA and they may not all return the same result so it’s important to know the difference. Finding the last row using VBA and the worksheet’s UsedRange property is probably the most common use case that I’ll cover in this article. This is the method I use the most because I generally only have one data set per sheet in my workbooks. If you have multiple tables or data sets on a single sheet, then this method may not be the best for you.
Sub LastRowUsedRange()
Dim LastRow As Long, LastCol As Long
Dim Sheet As Worksheet
Set Sheet = ActiveSheet
'Ignores Blank Rows
LastRow = Sheet.UsedRange.Rows.Count
LastCol = Sheet.UsedRange.Columns.Count
MsgBox "The Last Row is " & LastRow & ". The Last Column is " & LastCol & "."
End Sub
The UsedRange property is similar to using Ctrl + Shift + End from cell A1 on a spreadsheet. From there you can access properties about that range, such as the number of columns. It’s important to remember that this is ALL the rows and columns in the range. So, if you’re iterating through the data, make sure you remember to account for the header row.
Finding the Last Row or Last Column from End of Sheet
Another approach to getting the last row or last column in VBA is to find the first row from the bottom of the spreadsheet, or the first column from right of the spreadsheet. This is not an approach I recommend because, if you have a blank cell in your last row or last column and you use that row/column to measure on, you could potentially return an incorrect value. However, this was a common approach for a long time so it’s worth knowing in case you run into it or want to use it.
Sub LastRowEndOfSheet()
Dim LastRow As Long, LastCol As Long
Dim Sheet As Worksheet
Dim Cell As Range, CellTwo As Range
Set Sheet = ActiveSheet
Set Cell = Sheet.Cells(Sheet.Rows.Count, 1).End(xlUp)
Set CellTwo = Sheet.Cells(1, Sheet.Columns.Count).End(xlToLeft)
'Ignores Blank Rows
LastRow = Cell.Row
LastCol = CellTwo.Column
MsgBox "The Last Row is " & LastRow & ". The Last Column is " & LastCol & "."
End Sub
In this example, you’re simply starting with a cell at the end of the sheet and using the End method (this is just like using the Ctrl + arrow key to move, or pressing End and then the arrow key) to navigate to the first non-blank cell in a specified direction. Again, be on alert if you’re data could potentially have blanks.
Finding the Last Row or Last Column from Beginning of Sheet
On the flip side of the previous method, you can perform a similar action from the beginning of a data set. As with the previous one, this approach will stop at the cell before the first blank cell. I find this approach useful on rare occasions where I may have multiple data sets on a single tab or I know there is a blank row in my data set and I only need the first section of data iterated. The code is very similar to the previous subroutine.
Sub LastRowBeginOfSheet()
Dim LastRow As Long, LastCol As Long
Dim Sheet As Worksheet
Dim Cell As Range, CellTwo As Range
Set Sheet = ActiveSheet
Set Cell = Sheet.Cells(1, 1).End(xlDown)
Set CellTwo = Sheet.Cells(1, 1).End(xlToRight)
'Ignores Blank Rows
LastRow = Cell.Row
LastCol = CellTwo.Column
MsgBox "The Last Row is " & LastRow & ". The Last Column is " & LastCol & "."
End Sub
These are generally the only methods you will need to navigate your data in Excel, but I’d also like to go over using these methods on named ranges and tables. This is my preferred approach as it makes the code more readable in larger projects.
Finding the Last Row or Last Column in a Table
Many times, we store data in tables to take advantage of some of the benefits that tables offer. Using VBA, it’s incredibly easy to find the number of rows or columns in a table.
Sub LastRowTable()
Dim LastRow As Long, LastCol As Long
Dim Sheet As Worksheet
Set Sheet = ActiveSheet
'Ignores Blank Rows
LastRow = Sheet.ListObjects("Table1").Range.Rows.Count
LastCol = Sheet.ListObjects("Table1").Range.Columns.Count
MsgBox "The Last Row is " & LastRow & ". The Last Column is " & LastCol & "."
End Sub
Once you have the table object, you can select a range object from that table that then will allow you to access all of the properties from range you need, such as column or row counts.
Finding the Last Row or Last Column in a Name Range
Doing this in a named range is also incredibly easy if you have read through the previous examples, the only difference is you define your range object first with the named range.
Sub LastRowNamedRange()
Dim LastRow As Long, LastCol As Long
Dim Sheet As Worksheet
Dim MyData As Range
Set Sheet = ActiveSheet
Set MyData = Sheet.Range("nbaData")
'Ignores Blank Rows
LastRow = MyData.Rows.Count
LastCol = MyData.Columns.Count
MsgBox "The Last Row is " & LastRow & ". The Last Column is " & LastCol & "."
End Sub
In summary, each of these methods shown have their place, and there are a handful of others out there as well that I did not cover. Generally, I don’t think that these are needed any more. If you find yourself in a situation where one of the above approaches do not work, I would recommend rethinking your data format before looking for other alternatives.
Lastly, I just want to point out that you should always thoroughly test your VBA code for calculating last row and last column. Finding the last row using VBA (or last column) is important and missing data from your code can lead to incorrect results from your code, no matter how well the rest of it is written.
If you have any questions, please drop them in the comments or shoot me an email from the contact section. All of the code and some example data is included in the example file section for you to use if you’re a member of the site. It’s easy to sign up and I won’t ever spam you with unwanted ads. Until next time….
Cheers,
R
No Comments