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.
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(“A2:D8”)- 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:
Range(Cells(2,1),Cells(8, 4))Today, we will demonstrate some examples using the second syntax of range.
How to Set Range Using Row and Column Numbers in Excel: 4 Practical Examples
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 Basic window.
- 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 Code window.
- After that, type the code in the Code window:
Sub Select_a_Range()
Range(Cells(5, 2), Cells(8, 4)).Select
End 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 Basic window.
- 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
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 Basic window.
- Secondly, select Insert and then Module in the Visual Basic window. The Module window will occur.
- Now, type the code in the Module window:
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 Basic window.
- 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
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 Basic window.
- 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 Code window.
- Next, type the code in the Code window:
Sub Variable_Row_and_Column_Number()
Dim Row_Num1 As Integer
Dim Column_Num1 As Integer
Dim Row_Num2 As Integer
Dim Column_Num2 As Integer
Row_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)).Select
End 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 run it.
- 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
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 Basic window.
- 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 Code window.
- Then, type the code in the Code window:
Sub Select_Range_from_Another_Worksheet()
Dim xRg As Range
Dim xSht As Worksheet
Set xSht = Worksheets("Specific Value")
With xSht
.Cells(1, 7).Select
Set xRg = .Range(.Cells(5, 3), .Cells(8, 4))
End With
xRg.Select
End Sub
- Now, save the code by pressing Ctrl + S on the keyboard and then, close the Visual Basic window.
- After that, select Macros from the Developer tab in the ribbon.
- In the following, select the desired code and Run it from the Macro window.
- Finally, the range C5:D8 of the sheet named Specific Value will be selected.
Read More: Excel Macro: Convert Multiple Rows to Columns
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.
Download Practice Book
Download the practice book here.
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.
