VBA & Macro Problems

How to Find the Last Used Column in an Excel Worksheet with VBA

Natalie Taylor Oct 10, 2026 868 views

Question details

The user needs to identify the final column containing data across an Excel worksheet using VBA, particularly when rows contain varying numbers of populated columns.

How to Find the Last Used Column in an Excel Worksheet with VBA

Product: Excel
Device & OS: not provided
Scenario: Writing a VBA macro to determine the dynamic width of data in a worksheet for further processing or formatting.
Observed behavior: The user wants a reliable VBA method to programmatically locate the last column with data across the entire active sheet.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have the Developer tab enabled to access the VBA Editor.

Solution 1Recommended

Use the UsedRange Property in VBA

This is the most straightforward method to count the number of columns used in the active sheet.

The UsedRange property in Excel VBA identifies the rectangular area of the worksheet that contains data or formatting.

While effective, be aware that if your data does not start in column A, or if there is leftover formatting outside your data range, the column count returned may need to be adjusted or verified.

Open the VBA Editor

Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click 'Insert' from the top menu and select 'Module' to create a blank script window.

Enter the VBA Code

Type the code to declare your variable and fetch the count. For example: `Dim iCol As Integer` followed by `iCol = ActiveSheet.UsedRange.Columns.Count`.

Output or Use the Variable

You can verify the result using a message box by adding `MsgBox "The last used column is " & iCol`.

UsedRange Behavior: If your actual data starts in Column B instead of Column A, Columns.Count will give you the total number of columns in the used range, not necessarily the absolute column index number.

Solution 2

Use Cells and Columns.Count for Exact Column Index

An alternative method that reliably finds the absolute last column index regardless of where the data starts or empty formatted cells.

Advanced Spreadsheet Editor

Run Macros and Find Used Columns in WPS Spreadsheet

WPS Office provides excellent support for VBA macros, allowing you to run your existing Excel scripts seamlessly. You can easily find used ranges, automate tasks, and process data with full compatibility.

1. Open WPS Spreadsheet: Launch WPS Office and open your macro-enabled (.xlsm) workbook.
2. Enable Developer Tools: Go to the 'Developer' tab in the top ribbon and click on 'Macros' or 'Visual Basic'.
3. Run Your Code: Paste your `ActiveSheet.UsedRange.Columns.Count` code into the editor and execute it just like you would in Excel.

High compatibility with Microsoft Excel VBA macros and .xlsm formats.Easily run ActiveSheet.UsedRange and other standard VBA properties.Free to download with a lightweight, tabbed user interface.Built-in Developer tools for writing and debugging code.

microsoft office alternative - wps office

Frequently Asked Questions

Why does UsedRange return a column past my actual data?

This happens if a cell outside your data area has leftover formatting (like a background color or border). Excel considers formatted cells as 'used'. To fix this, delete the empty columns to the right of your data.

How do I find the last used row in VBA?

Similar to finding columns, you can use `iRow = ActiveSheet.UsedRange.Rows.Count` to return the total number of rows within the used range of the active worksheet.

What if my data doesn't start in Column A?

If your data starts in Column C, `UsedRange.Columns.Count` will return the total width of the range, not the absolute column letter. To get the absolute last column, use `ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1`.

Excel & Data Problems