You can use the following methods to get a column number from a range in Excel using VBA:
Method 1: Get Column Number from Specific Range
Sub GetColumnNumber()
colNum = Range("D7").Column
MsgBox colNum
End Sub
This particular macro will display a message box with the column number that corresponds to cell D7, which would be 4 since D is the fourth column in the sheet.
Method 2: Get Column Number from Currently Selected Range
Sub GetColumnNumber()
colNum = Selection.Column
MsgBox colNum
End Sub
This particular macro will display a message box with the column number that corresponds to the currently selected range in Excel.
For example, if you have cell B3 selected when you run this macro then a message box will appear with the value 2 in it since column B is the second column in the sheet.
The following examples show how to use each method in practice.
Example 1: Get Column Number from Specific Range
Suppose we would like to get the column number for the cell reference D7.
We can create the following macro to do so:
Sub GetColumnNumber()
colNum = Range("D7").Column
MsgBox colNum
End Sub
When we run this macro, we receive the following output:
The message box displays a value of 4, which is the column number for the cell reference D7.
Example 2: Get Column Number from Currently Selected Range
Suppose we would like to get the column number from the currently active cell.
We can create the following macro to do so:
Sub GetColumnNumber()
colNum = Selection.Column
MsgBox colNum
End Sub
Suppose we currently have cell B3 selected.
When we run this macro, we receive the following output:
The message box displays a value of 2, which is the column number for the currently active cell of B3.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Get Row Number from Range
VBA: How to Find Value in Column
VBA: How to Count Number of Used Columns
