Excel Macro: VBA to Get the Row Count and Letter of the Last Column

You can get the last cell in Excel using the following line of VBA code:

ActiveCell.SpecialCells(xlLastCell).Select

However if your dataset was modified (specifically, if you deleted rows), then doing that won’t get the right value. It’ll return what the value was before the data was modified.

Instead, I use the script below.

Get the Count of Rows

I often need to loop through the entire dataset. I first select Cell A1, and then I continue down to the next row until there’s a blank. Now I know how many rows are in the dataset, and I can create a FOR loop that goes from 1 to that number.

The function also takes in a parameter incase you didn’t want to start in Column A.

EDIT: Updated solution at the end of this article

Get the Letter of the Last Column

Sometimes I need to select a range of cells, and I have to specify the right-most column. Again, starting in Cell A1, I move right until I hit a blank. Once I do, I note the cell address. It’ll be in the format of “$Z$10”.

I can separate the Cell letter from the number by splitting on the “$”. But I have to not include the first item of the array, since the string begins with “$”. So I immediately remove the first character from the string.

Again, the function can accommodate starting in a row that isn’t the first one.

The Code

Note in both cases the last thing returned is set equal to the name of the function:

Update for “How many rows are there?”

I like the code below better. It still assumes that the first column of data will not have a blank value.

Also be mindful of how big your dataset is. If it’s more than ~32,000 rows, then declaring it as an integer won’t be enough; that’s why I’m using long.