VBA & Macro Problems

How to Use Column Numbers Instead of Letters in Excel VBA

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to reference Excel columns using numbers rather than letters in VBA, specifically to increment the column index by two within a loop.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Writing a VBA macro that iterates through columns systematically based on numerical patterns.
Observed behavior: Standard range references use letters (e.g., Range("D2")), making mathematical operations or looping across columns difficult without complex letter-conversion formulas.

Before you start

Ensure your Developer tab is enabled in the ribbon and save your workbook as a Macro-Enabled Workbook (.xlsm) before testing loops to prevent accidental code loss.

Solution 1Recommended

Use the Cells(RowIndex, ColumnIndex) Syntax

The most efficient way to use column numbers in VBA is by utilizing the Cells property, which natively accepts numeric arguments for both rows and columns.

The Cells property allows you to reference a specific cell by its row number and column number. For example, Cells(2, 4) targets row 2, column 4, which is equivalent to cell D2. This makes it incredibly easy to loop through columns mathematically.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic Editor, and insert a new Module via Insert > Module.

Declare your column variable

Start your macro and declare a variable to hold the column number, such as: Dim c As Long

Create a numerical loop

Write a For loop that steps through your desired column numbers. To increment by two, use the Step keyword: For c = 1 To 15 Step 2

Apply the Cells property

Inside the loop, reference the cell dynamically using Cells(row_number, c). For example, to write the column number into row 2, use: Cells(2, c).Value = c

Close and execute the loop

Close the loop with Next c and press F5 to run the macro. The values will populate across the columns numerically.

Index Order Warning: Remember that the Cells property takes the row number first, followed by the column number. Writing Cells(Column, Row) will result in incorrect cell references.

Advanced Macro Support in WPS

Write and Execute VBA Macros Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides robust built-in support for VBA macros, allowing you to use the exact same syntax—like the Cells property—without rewriting your code. It's a highly compatible alternative for automating your repetitive tasks.

1. Open your workbook in WPS: Launch WPS Spreadsheet and open your macro-enabled workbook.
2. Access the Developer Tab: Navigate to the Developer tab on the top ribbon.
3. Launch the VBA Editor: Click on 'Visual Basic' or press ALT + F11 to open the WPS Macro Editor.
4. Run your macro: Paste or write your standard VBA code using numeric column references and click the 'Run' icon.

Fully compatible with Microsoft Excel VBA syntax and .xlsm formats.Execute column-number loops and dynamic range selections effortlessly.Familiar Developer tab interface for writing, debugging, and running code.Lightweight application with fast load times for heavy macro workbooks.

microsoft office alternative - wps office

Frequently Asked Questions

How do I select an entire column using its number in VBA?

You can use the Columns property followed by the number. For example, Columns(4).Select will select the entirety of column D.

Can I convert a column number to its corresponding letter in VBA?

Yes. You can extract the letter using the Address property. The expression Split(Cells(1, column_number).Address, "$")(1) will return the correct column letter.

Can I use column numbers with the Range object instead of just single Cells?

Yes, you can combine the Range object with the Cells property. To reference a block of cells from A1 to D5 using numbers, you would write Range(Cells(1, 1), Cells(5, 4)).

Why should I declare my column variable as Long instead of Integer?

While Integer can handle up to 32,767 (and Excel has 16,384 columns), using Long is the modern VBA standard best practice. It processes efficiently in 32-bit and 64-bit systems and prevents overflow errors if you later reuse the variable for row counts.

Excel & Data Problems