You can get the last cell in Excel using the following line of VBA code:
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.
Note in both cases the last thing returned is set equal to the name of the function: