Excel VBA to Set Range Using Row and Column Numbers (4 Examples) (2023)

In this article, we will learn to set the range using row and column numbers with VBA in Excel. Generally, we use the starting and ending cells as references in the range syntax. But, today, we will show how we can express a range using row and column numbers. So, without any delay, let’s start the discussion.

Download Practice Book

Download the practice book here.

The Range Syntax

There are a couple of syntax ranges in Excel. But, all syntax serves the same purpose. They are given below:

• Range(“Starting Cell:Ending Cell”)

Here, you need to type the starting and ending cells of a range. Suppose, a range starts from Cell A2 and ends at Cell D8. Then, the expression will be:

• Range(Cells(row number,column number),Cells(row number, column number))

If a range starts from Cell A2 and Cell D8, then the expression will be:

Today, we will demonstrate some examples using the second syntax of range.

4 Examples of VBA to Set Range Using Row and Column Numbers in Excel

To explain the examples, we will use a dataset that contains information about the Department and the Age of some employees.

1. Select a Range Using Row and Column Numbers in Excel

In the first example, we will select a range using row and column numbers with Excel VBA. Follow the steps below to know the process.

STEPS:

• First of all, go to the Developer tab and select Visual Basic. It will open the Visual Basicwindow.

• Secondly, right-click on the sheet where you want to apply the VBA code. A drop-down menu will occur.
• Then, select View Code from there. It will open the Codewindow.

• After that, type the code in the Code window:

Sub Select_a_Range() Range(Cells(5, 2), Cells(8, 4)).SelectEnd Sub

Here, Cells(5,2) denotes the first cell of the range, Cell B5. Cell B5 is located at the intersection of row 5 and column 2. Similarly, Cells(8,4) denotes Cell D8 as the last cell of the range located at the intersection of row 8 and column 4.

• Now, press Ctrl + S to save the code.
• After saving the code, press the F5 key to run the code.
• Next, close the Visual Basicwindow.
• Finally, you will see the range B5:D8 is selected in the worksheet.

Read More: Excel VBA: Get Row and Column Number from Cell Address (4 Methods)

2. Set a Specific Value in a Range Using Row and Column Numbers

We will set a specific value in a range using row and column numbers in this example. For that, we will use the same dataset. But, range C5:D10 will be empty.

Let’s follow the steps below to know more.

STEPS:

• In the first place, select Visual Basic from the Developer window to open the Visual Basicwindow.

• Secondly, select Insert and then Module in the Visual Basicwindow. The Module window will occur.

• Now, type the code in the Modulewindow:

Sub Set_a_Specific_Value()Sheet3.Range(Sheet3.Cells(5, 3), Sheet3.Cells(10, 4)).Value = "Not Filled"End Sub

Here, we have selected the range C5:D10 in Sheet3. This code will work only on sheet 3. To fill the cells with a value or text, we have used the .Value command.

• After that, press Ctrl + S to save the code.
• Then, hit the F5 key to run the code and then, close the Visual Basicwindow.
• In the end, you will see results like the picture below in Sheet 3.

Read More: [Fixed!] Rows and Columns Are Both Numbers in Excel

Similar Readings

• How to Switch Rows and Columns in Excel Chart (2 Methods)
• Add Multiple Rows and Columns in Excel (Every Possible Way)
• How to Transpose Column to Multiple Rows in Excel (6 Methods)
• Convert Multiple Rows to Columns in Excel (9 Ways)
• How to Transpose Multiple Columns to Rows in Excel

3. Apply Variable Row and Column Numbers to Select a Range in Excel

In this example, we apply variable row and column numbers to select a range. The code will take inputs from the users. Again, we will use the same dataset. So, without further ado, let’s jump to the steps.

STEPS:

• Firstly, go to the Developer tab and select Visual Basic. This will open the Visual Basicwindow.

• In the second step, right-click on the sheet where you want to apply the code. A drop-down menu will occur. Select View Code from there. It will open the Codewindow.

• Next, type the code in the Code window:

Sub Variable_Row_and_Column_Number()Dim Row_Num1 As IntegerDim Column_Num1 As IntegerDim Row_Num2 As IntegerDim Column_Num2 As IntegerRow_Num1 = InputBox("Enter the row number of the first cell")Column_Num1 = InputBox("Enter the column number of the first cell")Row_Num2 = InputBox("Enter the row number of the last cell")Column_Num2 = InputBox("Enter the column number of the last cell")Sheet4.Range(Cells(Row_Num1, Column_Num1), Cells(Row_Num2, Column_Num2)).SelectEnd Sub

Here, Row_Num1 and Column_Number1 are the rows and column numbers of the first cell of the range respectively. And Row_Num2 and Column_Number2 are the rows and column numbers of the last cell.

• Now, press Ctrl + S to save the code and hit the F5 key to runit.
• After running the code, a message box will appear. It will ask for the row number of the first cell of the range. Type the row number and press Enter or click OK. In this case, the first cell of the range is C5.

• Another message box will appear. Here, type the column number of the first cell of the range.

• In the third message box, you need to enter the row number of the last cell and click OK. Here, the last cell of the range is D10.

• In the fourth message box, you need to type the column number of the last cell and click OK to proceed.

• Finally, range C5:D10 will be selected.

Read More: [Fixed!] Missing Row Numbers and Column Letters in Excel (3 Solutions)

4. Select a Range from Another Worksheet with Row and Column Numbers

In the last example, we will select a range from another worksheet using row and column numbers. Here, we will embed the VBA code in Sheet 5 and select a range from the sheet named Specific Value. Let’s pay attention to the steps below to learn more.

STEPS:

• In the beginning, select Visual Basic from the Developer tab to open the Visual Basicwindow.

• Secondly, right-click on the sheet where you want to apply the VBA code. A drop-down menu will occur.
• Select View Code from there. It will open the Codewindow.

• Then, type the code in the Code window:

Sub Select_Range_from_Another_Worksheet()Dim xRg As RangeDim xSht As WorksheetSet xSht = Worksheets("Specific Value")With xSht .Cells(1, 7).Select Set xRg = .Range(.Cells(5, 3), .Cells(8, 4))End WithxRg.SelectEnd Sub

• Now, save the code by pressing Ctrl + S on the keyboard and then, close the Visual Basicwindow.
• After that, select Macros from the Developer tab in the ribbon.

• In the following, select the desired code and Run it from the Macrowindow.

• Finally, the range C5:D8 of the sheet named Specific Value will be selected.

Read More: Excel Macro: Convert Multiple Rows to Columns (3 Examples)

Advantages of Using Row and Column Numbers to Set a Range

There are some advantages when you set a range using row and column numbers in Excel.

• We can easily change the arguments inside the syntax of the range. So, it is very flexible.
• It is very easy to understand.
• You can pass more than 1 value as an argument.

Things to Remember

The only thing you need to remember while setting range using row and column numbers is that you have to be extra careful about the row and column number of a cell. If you enter the wrong row or column number, then the whole range will be incorrect.

Conclusion

In the above discussion, we have demonstrated 4 easy examples to Set Range Using Row and Column Numbers with Excel VBA. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.

Related Articles

• How to Hide Rows and Columns in Excel (10 Ways)
• How to Add Rows and Columns in Excel (3 Easy Methods)
• Move Row/Column in Excel Without Replacing Existing Data (3 Best Ways)
• How to Switch Rows and Columns in Excel (5 Methods)
• Freeze Rows and Columns at the Same Time in Excel