Table of Contents
To find the last used column in Excel using VBA, the code will need to loop through each column in the worksheet and determine if it has been used or not. If the cell in the column is empty, it will loop through the next column until it finds a cell with data in it. Once the loop finds a cell with data, it will set the variable for the last used column in the worksheet. This variable can then be used to determine the last used column in the worksheet.
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:
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.
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 .