How to Use VBA Range Based on Column Number in Excel

Get FREE Advanced Excel Exercises with Solutions!

Using VBA code is an effective and intelligent way to deal with various tasks in Excel. In this article, you will learn how to use VBA Range based on the column number in Excel.

The GIF given below depicts the overview concept of selecting a range of data based on column number by incorporating VBA code.

excel vba range column number


VBA Range Object

The Range object in VBA can contain a single cell, multiple cells, rows, and columns within the Excel worksheet.

The hierarchy of the Range object is as below.

Application > Workbook > Worksheet > Range

This is how you should declare the Range object in VBA.

excel vba range column number


How to Use VBA 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.

Sample Dataset

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

Steps:

  • First of all, press Alt + F11 on your keyboard to open the Microsoft Visual Basic window.
  •  You can also do it by going to the tab Developer -> Visual Basic to insert VBA code.

Open Microsoft Visual Basic

  • Then, in the pop-up code window, from the menu bar, click Insert -> Module.

Insert Module to Write Code

  • Copy the following code and Paste it into the code window.
Sub SetRange()
    Range(Cells(4, 5), Cells(12, 7)).Select
End Sub

Here,

  • Cells(4,5) = 2nd row, 3rd column (Cell E4)
  • Cells(12,7) = 10th row, 5th column (Cell G12)

Your code is now ready to run.

 

Insert VBA Code

  • 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 E4 to G12 will be selected (see the picture below) based on the column number that we provided.

Select Range of Cells Using VBA

Read More: Find Value in Row and Return Column Number Using VBA in Excel


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, 4), Cells(4, 10)).Value2 = "Hello!"
End Sub

Here,

  • Cells(2,4) = 2nd row, 2nd column (Cell B4)
  • Cells(4,10) = 8th row, 4th column (Cell D10)

Your code is now ready to run.

Apply VBA Code

  • Then, Run the macro and all the cells from B4 to D10 now hold the value “Hello!

Insert VBA Code

Read More: VBA to Convert Column Number to Letter in Excel


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:

  • 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 x As Range
  Dim y As Worksheet
  Set y = Worksheets("Range") 
  With x
    .Cells(7, 7).Select 
    Set x = .Range(.Cells(5, 5), .Cells(9, 8))
  End With
  x.Select 
End Sub

Here,

  • Cells(5,5) = 3rd row, 3rd column (Cell E5)
  • Cells(9,8) = 7th row, 6th column (Cell H9)

Your code is now ready to run.

Apply VBA Code

Code Breakdown:

  • Set y = Worksheets(“Range”) function sets Range as the active worksheet and then x.Cells(7, 7). Select cell (7, 7) i.e. cell G7.
  • After that, Set x = .Range(.Cells(5, 5), .Cells(9, 8)) selects the range E5:H9.
  • Finally, Run the code.
  • As a result, all the cells from E5 to H9 from the worksheet name “Range” will be selected (see the picture below) based on the column numbers that we provided.
  • Select a Range from Different Worksheet

    Read More: How to Find Column Number Based on Value in Excel


    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 cells B4 to E13. We will extract the last column number from the range in the Return box, Cell G5.

    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()
        Dim x As Worksheet
        Dim y As Range
        Set z = Worksheets("Return") 
        Set y = z.Range("B4:E13") 
        z.Range("G5") = y.Column + y.Columns.Count - 1
    End Sub

    Your code is now ready to run.

    Insert VBA Code

    Code Breakdown:

    • First, this code sets Return as the active worksheet.
    • Then it sets variable y as the Range (B4:E13).
    • Finally, it counts the number of columns in the range and returns the last column number in cell G5.
    • After that, Run the macro.
    • And you will get the last column number, 5, in Cell G5.

    Return Column Number Using VBA

    Read More: How to Return Column Number of Match in Excel 


    Advantages of VBA Set Range

    • It is very easy to implement.
    • Additionally, 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.

    Practice Section

    Finally, we have provided a practice section on each sheet on the right side. If you want to practice, then use this section.

    Practice Section


    Things to Remember

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

    Download Workbook


    Conclusion

    In this article, you learned how to use VBA Range based on the column number in Excel. I hope this article has been very beneficial to you. However, if you have any queries, then feel free to ask any questions regarding the topic.


    Related Articles


    << Go Back to Column Number | Columns in Excel | Learn Excel

    What is ExcelDemy?

    ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
    Sanjida Ahmed
    Sanjida Ahmed

    Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

    1 Comment
    1. Hi, thanks for the information above… but, how to copy and paste starting from specific column cell to end of non-blank cell in the same column? Tried using 😛 through column P, it does not work..

      wk.Worksheets(“DATA”).Range(“P7:P”).copy
      ThisWorkbook.Worksheets(“SHEET1”).Range(“D8”).PasteSpecial xlPasteValues

      Appreciate for solution… thank you.

    Leave a reply

    Advanced Excel Exercises with Solutions PDF

     

     

    ExcelDemy
    Logo