Selecting a Range Using Variables

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

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.

You can easily modify the procedure. Instead of specifying the last row position, you can add the number of rows to the start_row.

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.

Now, you can use it inside the procedure. This time we are going to specify 4 variables, instead of 2.

Now, you can specify ranges using variables for rows and columns.

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

Posted in vba