VBA to Use Range Based on Column Number in Excel (4 Methods)

This section will discuss how to select a range based on column numbers, how to set a value in a range based on column numbers and how to return the last column number of a range in Excel using VBA macro.

1. VBA to Select a Range Based on Column Number in Excel

If you want to select a range based on column numbers in Excel from a fuzzy dataset like shown below, then you should continue reading this article to get the solution.

Steps to select a range from the dataset shown above using column numbers are described below.

Steps:

Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

In the pop-up code window, from the menu bar, click Insert -> Module.

Copy the following code and paste it into the code window.

Sub SetRange()
    Range(Cells(2, 3), Cells(10, 5)).Select
End Sub

Here,

Cells(2,3) = 2nd row, 3rd column (Cell C2)
Cells(10,5) = 10th row, 5th column (Cell E10)

Your code is now ready to run.

Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

The cell range from C2 to E10 will be selected (see the picture below) based on the column number that we provided.

2. VBA to Set Values in a Range Based on Column Number in Excel

You have learned how to select a range based on column numbers in the previous section. In this section, you will know how to set values in the range based on column numbers.

Steps:

Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
In the code window, copy the following code and paste it.

Sub SetValue()
    Range(Cells(2, 2), Cells(8, 4)).Value2 = "Hello!"
End Sub

Here,

Cells(2,2) = 2nd row, 2nd column (Cell B2)
Cells(8,4) = 8th row, 4th column (Cell D8)

Your code is now ready to run.

Run the macro and all the cells from B2 to D8 now hold the value “Hello!”

3. VBA to Select a Range from Different Worksheet Based on Column Number in Excel

Here you will learn how to select a range based on column numbers from a different worksheet in Excel.

Steps to do that are shown below.

Steps:

Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
In the code window, copy the following code and paste it.

Sub FixedRange()
  Dim iRng As Range
  Dim iSheet As Worksheet
  Set iSheet = Worksheets("Range") 'select the worksheet name"
  With iSheet
    .Cells(1, 7).Select 'selects cell G7 on worksheet
    Set iRng = .Range(.Cells(3, 3), .Cells(7, 6))
  End With
  iRng.Select 'selects range of cells C3:F7 on worksheet
End Sub

Here,

Cells(3,3) = 3rd row, 3rd column (Cell C3)
Cells(7,6) = 7th row, 6th column (Cell F7)

Your code is now ready to run.

Run the code and all the cells from C3 to F7 from the worksheet name “Range” will be selected (see the picture below) based on the column numbers that we provided.

4. VBA to Return the Last Column Number in a Range in Excel

In this section, we will learn about the VBA code on how to get the last column number in an active range in Excel.

Consider the following dataset that has data from Cell A1 to D10. We will extract the last column number from the range in the Return box, Cell F5.

Steps to do that are given below.

Steps:

Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
In the code window, copy the following code and paste it.

Sub ReturnLastColumnInRange()
    'declare variables
    Dim iSheet As Worksheet
    Dim iRng As Range
    Set iWS = Worksheets("Return") 'select the worksheet
    Set iRng = iWS.Range("A1:D10") 'set the range
    'return the last column number in a cell
    iWS.Range("F5") = iRng.Column + iRng.Columns.Count - 1
End Sub

Your code is now ready to run.

Run the macro and you will get the last column number, 4, in Cell F5.

Advantages of VBA Set Range

It is very easy to implement.
The arguments inside the Range object are not fixed. So we can modify the values of the argument according to our needs.
More than 1 value can be passed as arguments.

Things to Remember

CELLS properties in VBA can also be used to set the Range in VBA.
Object variables should be set as the reference of the object by the SET keyword.

Conclusion

This article showed you how to use Range to do different tasks in Excel based on the column number with VBA. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.