Sanjida Ahmed
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.
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 SubHere,
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.
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 SubYour code is now ready to run.
- Run the macro and all the cells from A1 to D5 now hold “Hello!”
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 SubYour 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!”
- 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 SubYour 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!”
Similar readings:
- How to Select Range Based on Cell Value VBA (7 Ways)
- Use the Range Object of VBA in Excel (5 Properties)
- How to Use VBA Range Offset (11 Ways)
- VBA Range with Variable Row Number in Excel (4 Examples)
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 SubYour 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!”
- 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 SubYour code is now ready to run.
- Run the macro and the 2nd and 4th columns from A1 to D5 now hold “Hello!”
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.
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.
