VBA Find Last Column

In this lesson, I presented a few ways to get the last column number. The first methods are easy, but then the code becomes more complicated when we have to deal with blank cells on the way.

Find the last column in the current row

The code returns the number of the last column from the active cell.

You can also set the cell, instead of using the active one.

The value is counted from 1, therefore the procedure returns 6.

If you want to get a letter value, instead of a number, you have to modify the code a little.

Now, instead of returning 6, the procedure returns F.

Find the last column in the current row (with blanks)

The code we are using works well until there is no blank cell on the way.

This time the code is going to return D, which is not what we want. What we want is F.

Code explanation

Now, instead of moving the cursor to the right from the first cell, we move the cursor to the last cell inside the row, and then to the left until it meets the first cell with a value. In our example it’s F.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba