VBA: How to Find Last Used Column


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:

VBA find last used column

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

Leave a Reply

Your email address will not be published. Required fields are marked *