Selecting a Range Using Variables

You can select a range in Excel using the range function:

Sub selectRange()
    Range("B2:E10").Select
End Sub

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

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

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

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

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.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 7,907