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.
1 2 3 |
Sub LastColumn() MsgBox Selection.End(xlToRight).Column End Sub |
You can also set the cell, instead of using the active one.
1 2 3 |
Sub LastColumnFromActiveRow() MsgBox Range("A1").End(xlToRight).Column End Sub |
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.
1 2 3 4 |
Sub LastColumnLetter() column_number = Selection.End(xlToRight).Column MsgBox Split(Cells(1, column_number).Address, "$")(1) End Sub |
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.
1 2 3 4 |
Sub LastColumnWithBlanks() column_number = Range("XFD1").End(xlToLeft).Column MsgBox Split(Cells(1, column_number).Address, "$")(1) End Sub |
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.