VBA to Set Range in Excel (7 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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


VBA Range Object

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

Read More: Excel VBA Range Function


How to Set Range in VBA Excel: 7 Examples

This section will discuss how to set a Range in a single cell, multiple cells, single row, multiple rows, single column, or 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”.


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


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

Read More: Excel VBA: Set Range by Row and Column Number


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

Read More: How to Use VBA to Set a Range Variable to Selection in Excel


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

Download Practice Workbook

You can download the free practice Excel workbook from here.


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

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.

Tags:

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

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo