VBA to Set Range in Excel (7 Examples)

In this article, you will learn how to Set Range for cells, rows and columns in VBA Excel.


Download Workbook

You can download the free practice Excel workbook from here.


VBA Range Object

The Range object in VBA can contain a single cell, multiple cells, rows, 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.

VBA to set range


7 Examples on how to Set Range in VBA Excel

This section will discuss how to Set a Range in a single cell, multiple cells, single row, multiple rows, single column, multiple columns and set a range through the Command Button in VBA Excel.

1. Set Range in a Single Cell in VBA

Here we will see how to Set Range in a single cell with VBA.

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 RangeOneCell()
Range("B2").Value = "Hello World"
End Sub

Here,

B2 = the cell where we want to set the value. You can set any cell reference number that you need.

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.

vba to set range in single cell

Cell B2 now contains the value “Hello World”.

Read more: VBA for Each Cell in Range in Excel


2. Set Range in Multiple Cells in VBA

Here we will see how to Set Range in multiple cells with VBA.

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 RangeCells()
Range("A1:D5").Value = "Hello!"
End Sub

Your code is now ready to run.

  • Run the macro and all the cells from A1 to D5 now hold “Hello!

vba to set range in multiple cells


3. Set Range in a Single Row in VBA

Here we will see how to Set Range in a single row with VBA.

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 RangeRows()
Dim iRow As Range
Set iRow = Range("A1:D5")
iRow.Rows(3).Value = "Hello!"
End Sub

Your code is now ready to run.

  • Run the macro and only the 3rd row from all the rows from A1 to D5 now hold “Hello!

vba to set range for single row

  • Rows(3).Value in the code gave access to the 3rd row of the specific range A1:D5.

4. Set Range in Multiple Rows in VBA

Here we will see how to Set Range in multiple rows with VBA.

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 RangeRows()
Dim iRow As Range
Set iRow = Range("A1:D5")
iRow.Rows(1).Value = "Hello!"
iRow.Rows(3).Value = "Hello!"
iRow.Rows(5).Value = "Hello!"
End Sub

Your code is now ready to run.

  • Run the macro and the 1st, 3rd and 5th rows from all the rows from A1 to D5 now hold “Hello!

vba to set range for multiple rows


Similar readings:


5. Set Range in Single Column in VBA

Here we will see how to Set Range in a single column with VBA.

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 RangeColumns()
Dim iCol As Range
Set iCol = Range("A1:D5")
iCol.Columns(2).Value = "Hello!"
End Sub

Your code is now ready to run.

  • Run the macro and only the 2nd column from all the columns from A1 to D5 now hold “Hello!

vba to set range for single column

  • iCol.Columns(2).Value in the code gave access to the 2nd column of the specific range A1:D5.

6. Set Range in Multiple Columns in VBA

Here we will see how to Set Range in multiple columns with VBA.

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 RangeColumns()
Dim iCol As Range
Set iCol = Range("A1:D5")
iCol.Columns(2).Value = "Hello!"
iCol.Columns(4).Value = "Hello!"
End Sub

Your code is now ready to run.

  • Run the macro and the 2nd and 4th columns from A1 to D5 now hold “Hello!

vba to set range for multiple columns


7. Set Range through Command Button in VBA

Here we will learn how to Set Range using Command Button in VBA.

Steps:

  • Go to Developer -> Insert -> Command Button.
    • Make sure, you have Design Mode turned on.

  • Drag around the Command Button in the sheet.

  • Double click on the button and it will take you to the code window, auto-generated with a VBA Sub-procedure code.

  • Inside the Sub, write your code and Save.

  • Go back to the worksheet of interest and click the Command Button. The result based on your code will appear in the worksheet.

vba to set range by command button


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

Conclusion

This article showed you how to Set Range in Excel VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.


You May Also Like to Explore

Tags:

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo