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

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(“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.


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 Basic window.

Select a Range Using Row and Column Numbers in Excel

  • 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.

Select a Range Using Row and Column Numbers in Excel

  • After that, type the code in the Code window:
Sub Select_a_Range()
    Range(Cells(5, 2), Cells(8, 4)).Select
End Sub

Select a Range Using Row and Column Numbers in Excel

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.

Select a Range Using Row and Column Numbers in Excel

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.

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

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.

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

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

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

  • 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

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

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.

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

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.

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

  • 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.

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

  • 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

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

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.

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

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

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

  • 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.

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

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

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

  • 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 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 (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

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo