How to Know the Column Number in Excel VBA

When working with Excel spreadsheets, identifying the column number of a cell is often a necessary task, especially while writing VBA scripts to automate Excel tasks. In this tutorial, we will learn how to determine the column number of a selected cell or range in Excel using VBA (Visual Basic for Applications). This can be useful in various scenarios, such as navigating through sheets programmatically or when addressing cells relative to others.

Example

Select cells in Column B.

Steps to Retrieve the Column Number

First, we need to write a VBA function that can be called to get the column number from a specified cell or range.

1. Writing the VBA Function

Begin by opening the Excel workbook in which you want to use the VBA function. Assuming you’re familiar with accessing the VBA editor, we’ll focus on the code itself.

Let’s name our VBA function GetColumnNumber. In this function, we’ll use the Column property of the Range object, which returns the column number of the first cell in the range.

The function above takes a single argument, rng, which is the range from which we want to extract the column number. The function returns this as an integer value.

2. Using the Function in A Macro

Next, let’s write a simple macro that uses the GetColumnNumber function to display the column number of the currently selected cell.

In this macro, we define a variable SelectedRange to hold the current selection. We then use a message box to display the column number, which we get by calling our GetColumnNumber function.

3. Running the Macro

Run the ShowColumnNumber macro by pressing F5 within the VBA editor or by assigning it to a button or a keyboard shortcut from within Excel. The message box will pop up, displaying the column number of the cell or range you have selected before running the macro.

Remember that column numbers are numeric, starting from 1 for column A, 2 for column B, and so on, regardless of how the actual column headers are displayed in Excel (such as A, B, C or AA, AB, AC).

Result

Conclusion

Being able to quickly determine the column number of a cell can save time and effort while writing more complex VBA code. With the simple VBA function and macro provided, you can automate tasks that require column number identification in Excel. This foundational knowledge is key when moving on to more advanced Excel VBA programming tasks.

Full Code