Selecting a Range With a Variable Row (or Column) Number
I’m going to start by reviewing a couple fundamentals about selecting ranges in Excel with VBA, then I’ll discuss methods for selecting ranges with variable row numbers.
Contents
Selecting Cells in Excel
One of the most common situations in Microsoft Excel is that you need to select a cell or a range of cells to perform an action. For example, even just to copy and paste content, you need to perform steps below one-by-one (I bet you never thought this was actually a complex task).
- Select the cell(s) to be copied
- Click on copy (or) ctrl + c
- Select the cell(s) into which the content needs to be pasted
- Click on paste (or) ctrl + v
In this process, we select one cell or multiple cells that are in continuous rows and columns. This continuous selection is called a “Range”. A range is generally identified by the starting cell reference in the left-side top corner and ending cell reference in the bottom right corner of the selection. For example, in the image below, the range is from C2 to E7. All the cells within this cell reference are selected.
The Range Expression
VBA offers a Range expression that can be used in selection of cells.
Syntax:
Range (“ < reference starting cell > : < reference ending cell >”)
(or)
Range ( Cells ( <row_number> , <col_number> ) , Cells ( <row_number> , <col_number> ) )
In other words, the range of cells selected in the image above can be expressed in any one of the following ways — for our example, we’ll assume that we are selecting the range on a sheet named “Wonders.”
Sheets("Wonders").Range("C2:E7").Select
Note that letters are used to represent column number and row is indicated by a number. There is a colon in between to two cell references and the entire parameter is encased in double quotes.
Sheets("Wonders").Range(Cells(2, 3), Cells(7, 5)).Select
In this case, double quotes are not used for the range. There is comma instead of the colon symbol, and the cell references are represented using the Cells expression and the row and column numbers.
Try it yourself. Name an Excel sheet “Wonders.” Try running the code below and check the “Wonders” sheet to see what has been selected.
Sub range_demo() Sheets("Wonders").Range("B2:F6").Select Sheets("Wonders").Range(Cells(2, 3), Cells(7, 5)).Select End Sub
Variable row numbers in the Range expression
So far we’ve tried to select a range of cells knowing the exact references of two cells that form the range. Now, it is time to see how to insert a dynamic or varying row or column number in the same expression.
The way to do it is simply with concatenation using double quotes and the ampersand (&) symbol.
Let’s look at some examples.
Color a range of cells up to a dynamically changing last row
In the example below, let’s find the last used row and dynamically use that row in the Range expression. This example will color the selected cells in green.
Sub range_demo() 'declare variable Dim lastrow As Integer 'initialize variable lastrow = ActiveSheet.UsedRange.Rows.Count 'Use the variable in the range expression to select Sheets("Wonders").Range("A2:C" &amp;amp;amp; lastrow).Select 'colour the selected cells in green With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 5296274 'green colour End With End Sub
Color the font of selected cells up to a specific row input by the user
Below is another example that uses the Cells expression to do the same thing. Here we will change the font color of the selected cells.
Sub range_demo() 'declare variable Dim row_num As Integer 'initialize variable - enter 6 while running the code row_num = InputBox("Enter the row number") 'Use the variable in the range expression to select only the first 5 rows of data - not the headers Sheets("Wonders").Range(Cells(2, 1), Cells(row_num, 3)).Select 'colour the font of selected cells in white With Selection.Font .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 End With End Sub
Use a different runtime number in both cell references
In this example, we’re still not hardcoding the cell references. Three rows starting from the row number input by the user are formatted in “Bold.”
Sub range_demo() 'declare variable Dim row_num As Integer 'initialize variable - enter 3 while running the code row_num = InputBox("Enter the row number") 'Use the variable in the range expression to select only the first 3 rows of data starting from the row_num input by the user Sheets("Wonders").Range(Cells(row_num, 1), Cells((row_num + 3), 3)).Select 'make the font of the selected cells to "Bold" Selection.Font.Bold = True End Sub
Concatenating both cell references of a simple range expression
Sub range_demo() 'declare variable Dim row_num As Integer 'initialize variable - enter 7 while running the code row_num = InputBox("Enter the row number") 'Use the variable in the range expression to select only the first 3 rows of data starting from the row_num input by the user Sheets("Wonders").Range("A" &amp;amp;amp; row_num &amp;amp;amp; ":C" &amp;amp;amp; row_num + 2).Select 'make the font of the selected cells to "Italics" Selection.Font.Italic = True End Sub
Conclusion
The Range expression in VBA is very useful for calculating and formatting cells in MS Excel. Not just row numbers, but also column numbers can be dynamically inserted to make the best use of the expression. In other words, instead of hard coding the reference directly, we can use a variable that holds the number in the expression.