You can select a range in Excel using the range function:
Sub selectRange() Range("B2:E10").Select End Sub
This code selects cells between columns B2 and E10.
Selecting rows using variables
If you are certain that particular columns are going to be used in a worksheet, but you want to control rows, you can type column letters and use variables for row numbers.
This code is going to have the same effect as before, but this time, instead of using numbers, we are going to use variables.
Sub selectRangeVariables() Dim start_row As Long, last_row As Long start_row = 2 last_row = 10 Range("B" & start_row & ":E" & last_row).Select End Sub
You can easily modify the procedure. Instead of specifying the last row position, you can add the number of rows to the start_row.
Sub selectRangeVariables() Dim start_row As Long, last_row As Long start_row = 2 last_row = start_row + 8 Range("B" & start_row & ":E" & last_row).Select End Sub
The start_row and last_row variables are used inside the Range function, joined with column letters.
Selecting columns using variables
There is also a way to specify the column number, instead of using letters. This code is more complicated because columns are not using numbers, therefore a function that converts numbers to column letters is necessary.
I wrote about such a conversion in one of my lessons.
First, create the function.
Function NumbersToColumns(myCol As Long) If myCol >= 1 And myCol <= 16384 Then iA = Int((myCol - 1) / 26) fA = Int(IIf(iA - 1 > 0, (iA - 1) / 26, 0)) NumbersToColumns = IIf(fA > 0, Chr(fA + 64), "") & _ IIf(iA - fA * 26 > 0, _ Chr(iA - fA * 26 + 64), "") & _ Chr(myCol - iA * 26 + 64) Else NumbersToColumns = False End If End Function
Now, you can use it inside the procedure. This time we are going to specify 4 variables, instead of 2.
Sub selectRangeVariables() Dim start_row As Long, last_row As Long Dim start_column As Long, last_column As Long start_row = 2 last_row = 10 start_column = 2 last_column = 5 Range(NumbersToColumns(start_column) & start_row & ":" & NumbersToColumns(last_column) & last_row).Select End Sub
Now, you can specify ranges using variables for rows and columns.