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.

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:

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store