Columns in Excel VBA: Select, Count, and Use Them

VBA & macros · Updated November 18, 2026

In Excel VBA, use a worksheet’s Columns property to refer to one or more entire worksheet columns, such as ws.Columns("B:B") or ws.Columns(2). Use a range’s Columns property when you want to work with columns inside a particular range. These references let a macro format, inspect, insert, delete, or loop through columns.

What Columns Means in VBA

Columns is a VBA property that returns a collection of columns. On a worksheet, ws.Columns refers to all its columns; with an argument, it identifies particular columns. For example, ws.Columns(2) means the entire second column, column B. A column reference is not the same as a cell range containing data in that column: it includes every row in the column, including blank cells.

Excel worksheets in current versions have 16,384 columns, from A to XFD, and 1,048,576 rows. That matters when a macro acts on a whole column: an operation can affect far more cells than the visible data. A whole-column reference is useful for formatting or locating a last-used cell, but a bounded range may be better when you want to process only a specific block.

VBA does not require a special “column object” declaration. A column reference can be assigned to a variable declared as Range, because a worksheet column is a range of cells. Qualifying the reference with a worksheet makes the target clear and avoids relying on whichever sheet happens to be active.

Three Common Ways to Reference Columns

These forms are common and serve slightly different needs:

  • ws.Columns("B:B") identifies all of column B by its letter. A range such as ws.Columns("B:D") identifies columns B through D.
  • ws.Columns(2) identifies the second worksheet column, B. A number is useful when the column position is calculated in code.
  • ws.Range("B2:D20").Columns refers to the columns contained in that range, rather than all rows of those worksheet columns.

Here, ws is a variable referring to a worksheet. For example, Set ws = ThisWorkbook.Worksheets("Data") points it to the worksheet named Data in the workbook containing the macro. The worksheet name must match the tab name. ThisWorkbook means the workbook where the VBA code is stored; ActiveWorkbook instead means the workbook currently in focus, which may be different.

When using Range.Columns, the numbering is relative to that range. In ws.Range("C5:E20").Columns(1), column 1 means the first column of the selected range, which is worksheet column C. This distinction is useful when processing a table or block, but can cause mistakes if code assumes the number always means the worksheet’s absolute column position.

How to Format or Loop Through Columns

A column reference can use many of the same operations as a cell range. For example, AutoFit adjusts column widths to fit cell contents. Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z.

Sub AutofitDataColumns()
    Dim ws As Worksheet
    Dim columnsToFit As Range

    ' Use the Data worksheet in the workbook containing this code.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set columnsToFit = ws.Range("B:D")

    ' Adjust the widths of columns B through D.
    columnsToFit.EntireColumn.AutoFit
End Sub

The example assumes a worksheet named Data and adjusts B, C, and D. The EntireColumn property makes the intent explicit: apply the action to the full worksheet columns. If the range is already a full-column reference, calling EntireColumn is not strictly necessary, but it can make code easier to interpret when the starting range might later change.

To apply an operation separately to each column, loop through the columns in a range:

Sub AutofitEachDataColumn()
    Dim ws As Worksheet
    Dim col As Range

    ' Set the worksheet explicitly rather than using the active sheet.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Visit columns B, C, and D one at a time.
    For Each col In ws.Range("B:D").Columns
        col.EntireColumn.AutoFit
    Next col
End Sub

A loop is useful when each column needs a different check or action. For a single identical formatting operation, applying it to the whole range at once is usually simpler. Also note that autofitting a full column considers its contents across the worksheet, not just a few rows you may be looking at.

Finding a Column’s Last Used Row

A frequent related task is finding the last populated cell in a particular column. The following pattern starts at the bottom of the worksheet and moves upward to the next non-empty cell:

Sub ShowLastRowInColumnB()
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Refer to the worksheet that contains the data.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Move up from the bottom of column B to its last non-empty cell.
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    MsgBox "Last used row in column B: " & lastRow
End Sub

ws.Rows.Count supplies the worksheet’s row count, so this approach does not depend on a fixed row limit in the code. The result is a row number, not a count of filled cells. If column B is completely blank, the expression returns 1; test the column for data separately if that distinction matters. Cells containing formulas count as occupied even if a formula displays an empty string.

This pattern finds the last non-empty cell in the chosen column, not necessarily the last row of a whole dataset. If column B has a gap near the bottom while another column contains more data, the result still reflects column B. Choose a column that is reliably populated for every record, or define the dataset boundaries using another method.

Choosing Between Columns and Cells

Use a whole-column reference when the operation genuinely applies to the full worksheet column: for example, setting a number format for future entries, autofitting a column, or finding its last entry. Use a bounded range such as ws.Range("B2:B500") when a macro should touch only known data rows. A bounded range can also make the scope of an operation clearer to someone maintaining the code.

For cell-by-cell work, Cells(rowNumber, columnNumber) is often more flexible than building a column letter into a string. For example, ws.Cells(5, 2) refers to B5. If the column number is stored in a variable, this avoids having to convert that number to a letter. By contrast, ws.Columns(2) is convenient when the operation concerns the entire column.

When working with an Excel table, consider whether the task should apply to the table’s data column rather than the entire worksheet column. Table columns expand with table data and have names, which can make structured data work easier to maintain. A whole worksheet column also includes cells outside the table, so it may be too broad for tasks such as clearing values or applying data-specific formatting.

Common Errors and How to Avoid Them

  • Using the wrong sheet: Unqualified code such as Columns("B:B") acts on the active sheet. Prefer ws.Columns("B:B") after setting ws to the intended worksheet.
  • Confusing a position with a letter: Columns(2) is B, not column 2 in a data table unless the table starts in A. For a range starting in C, Range("C5:E20").Columns(1) is C.
  • Applying an operation to too much data: Clearing or changing a whole column can affect notes, formulas, or information below the apparent dataset. Confirm the target range before running code.
  • Assuming a last-row result is a record count: The row number may include a header, blank gaps, or formulas. Decide whether the task needs a last occupied row, a count of non-empty cells, or a count of data records.
  • Misspelling a worksheet name: A missing sheet name causes a run-time error. Check the tab name and, for reusable code, add error handling if the sheet may not exist.

It is also safer to avoid selecting a column just to work on it. Code such as ws.Columns("B:B").AutoFit acts directly on the intended column. Selecting first can change the active sheet or selection and make a macro more dependent on the user’s current workbook state.

Running VBA in Excel for Microsoft 365

VBA macros run in the desktop versions of Excel for Windows and Mac; Excel for the web can open a macro-enabled workbook but cannot create, run, or edit its VBA macros. On Windows, open the Visual Basic Editor with Alt+F11, or choose Developer > Visual Basic if the Developer tab is available. In the editor, insert a standard module, paste a procedure, and run it while the intended workbook is open. Macro security settings may block macros, so only enable code from a source you trust.

Excel for Mac also supports VBA, though some editor controls and keyboard shortcuts differ. If a Windows shortcut does not work on a Mac keyboard, open the Visual Basic Editor from Excel’s Developer tab instead. VBA code that relies on Windows-only features, such as certain external libraries or operating-system calls, may need changes to work on Mac; the basic worksheet Columns and Range properties described here are part of Excel’s object model.

Frequently Asked Questions

How do I select a column in Excel VBA?
Use ws.Columns("B:B").Select for column B after setting ws to the worksheet. Usually, it is better to act directly on ws.Columns("B:B") without selecting it.

What does Columns(1) mean in VBA?
For a worksheet’s Columns property, Columns(1) means the first worksheet column, A. For a range’s Columns property, 1 means the first column within that range.

How do I refer to multiple columns?
Use a range such as ws.Columns("B:D") to refer to entire columns B through D, or ws.Range("B2:D20").Columns to work with the columns in a bounded block.

How can I count the columns in a range?
Use the range’s Columns.Count property. For example, ws.Range("B2:D20").Columns.Count returns 3 because the range spans three columns.

Can VBA work with columns in Excel for the web?
No. Excel for the web does not run or edit VBA macros. Use the Excel desktop app for Windows or Mac to work with VBA; a macro-enabled workbook can still be stored or opened online.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.