You can use the following basic syntax in VBA to find the last column used in an Excel sheet:
Sub FindLastColumn()
Range("A14") = Cells.Find("*",Range("A1"),xlFormulas,xlPart,xlByColumns,xlPrevious,False).Column
End Sub
This particular example finds the last used column in the current sheet and returns the result in cell A14.
If you would instead like to display the last column in a message box, you can use the following syntax:
Sub FindLastColumn()
Dim LastCol As Long
LastCol=Cells.Find("*",Range("A1"),xlFormulas,xlPart,xlByColumns,xlPrevious,False).Column
MsgBox "Last Column: " & LastCol
End Sub
The following examples shows how to use each of these methods in practice.
Related: VBA: How to Find Last Used Row
Example 1: Find Last Column Using VBA and Display Results in Cell
Suppose we have the following dataset in Excel that contains information about various basketball players:
We can create the following macro to find the last column used in this Excel sheet and display the result in cell A14:
Sub FindLastColumn()
Range("A14") = Cells.Find("*",Range("A1"),xlFormulas,xlPart,xlByColumns,xlPrevious,False).Column
End Sub
When we run this macro, we receive the following output:
Notice that cell A14 contains a value of 2.
This tells us that the last used column in this particular sheet is column 2.
It’s also worth noting that if you have empty columns before a used column, this macro will still find the last used column.
For example, suppose we run the macro on the following dataset:
Cell A14 contains a value of 5 because this is the last column with values in it.
Example 2: Find Last Column Using VBA and Display Results in Message Box
Suppose we would instead like to find the last used column in a sheet and display the column number in a message box.
We can create the following macro to do so:
Sub FindLastColumn()
Dim LastCol As Long
LastCol=Cells.Find("*",Range("A1"),xlFormulas,xlPart,xlByColumns,xlPrevious,False).Column
MsgBox "Last Column: " & LastCol
End Sub
When we run this macro, we receive the following output:
The message box tells us that the last used column in the sheet is column 2.
Note: You can find the complete documentation for the VBA Find method here.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Calculate Average Value of Range
VBA: How to Count Number of Rows in Range
VBA: How to Remove Duplicate Values in Range
