How to Use Ranges in VBA

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

When you’re working in VBA, you’ll have to tell Excel which cells you want a particular command to apply to.

To do that, you’ll use Ranges.

A range is simply a cell or collection of cells that you tell Excel to pay attention to.

Notice that I didn’t say “that you select.” That’s an important distinction, as you’ll see shortly.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s take a look at how to refer to cells with VBA, and then a couple things you can do with those cells.

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

How to refer to cells with the Range object

What’s an object?

In programming, an object is a container for data. In Excel’s VBA, an object represents an Excel element.

We’ll be creating an object and then using its methods to do things with it.

Don’t worry too much about objects and methods yet. You’ll get used to them as you keep learning VBA.

We’ll start with the basics: how to refer to cells using VBA’s Range object.

If you’d like to follow along, open a blank workbook, go to the Developer tab, click Visual Basic, and click Insert > New Module.

In the blank module, type the following:

Sub Refer()
  Range("B1")
End Sub

This tells Excel to look at cell B2.

range-one-value

You’ll notice that if you now run this macro from the Macros menu (accessible via Alt + F11 or the Developer tab), you’ll get an error.

vba-range-error

That’s because referring to a cell and not taking action doesn’t actually do anything. Let’s tell Excel to do something with that cell.

Change your VBA code to this:

Sub Refer()
  Range("B1").Value = "Welcome to Spreadsheeto!"
End Sub

Now run the macro from the Macros menu again:

vba-refer-value

Using “.Value”, the equals sign, and some text in quotation marks is a simple way to set the value of a range.

You can also do this with only the equals sign, but it’s a good habit to start using “.Value” right away.

Because we’re talking about Range, you might be wondering if you can use this sub to refer to multiple cells. And indeed you can:

Range("B1:B4").value = "Welcome to Spreadsheeto!"

Run the macro, and you’ll see that all four of these cells say “Welcome to Spreadsheeto!”

vba-range-value

You can include multiple ranges like this:

Range("B1:B4,C4:C8")

And you can even use named ranges:

Range("Dates")

How to refer to cells with the Cells object

The Cells object refers to cells using row and column index numbers instead of regular cell designations. Here’s an example:

Cells(3, 2).Value = "Spreadsheeto VBA lessons"

When we run this macro, we get “Spreadsheeto VBA lessons” in cell B3.

vba-cells-value

The row index, at 3, specifies row 3. The column index, at 2, specifies column B3. That’s how we get to B3.

As you can see, the “.Value” method works the same way with Cells as it does with range.

Why use Cells instead of Range?

In general, you’ll want to use Cells when you’re iterating through a loop. If you don’t know what that means, don’t worry—you can read about it in another lesson.

For now, just know that the Cells object is usually used when you need to refer to a number of cells in succession, instead of all at the same time.

Using the Select method with Ranges

We saw how to use the Value method; now let’s look at another useful one.

This one is called Select, and it performs a very simple function: it selects the cells you’ve referred to with the Range (or Cells) object.

Here’s an example:

Range("A1:D2").Select

When you run the macro, you’ll see that the cells defined by the range have now been selected:

vba-range-select

Selecting rows in a range

For this section and the next, we’re going to define a range using a variable. We won’t talk about variables here, so don’t worry about the syntax.

Just know that in the following macros, when you see “newRange”, it’s referring to the range A1:D4.

Kasper Langmann, Co-founder of Spreadsheeto
set-newrange-variable

You can use the Rows method to refer to an entire row within a range. Just use the Rows method and give it the row number you’d like to refer to as an argument.

Here’s an example:

newRange.Rows(2).Select

You can use other methods besides Select, but it’s a good one for showing which cells we’re referring to:

newrange-rows-select

In our example, we selected row 2, and Excel selected A2:D2. That’s the second row in our selection.

If newRange referred to the range C4:F6, the second row would have been different:

newrange-select

That’s because the Rows method refers to the row within the object that comes before (in the same line), not within the entire spreadsheet.

Selecting columns in a range

Selecting a column works exactly the same way. Just use the Columns method.

Here’s an example:

newRange.Columns(3).Select

Here’s what we get:

newrange-select-columns

Again, remember that the number you use refers to the column within your selection, not the spreadsheet at large.

If you want to select rows and columns in the spreadsheet at large, don’t write newRange before it:

Rows(3).Select   <-- this selects the entire row 3 of the spreadsheet

Columns(2).Select <– this selects the entire column 2 of the spreadsheet

Range(“B:B”).Select <– this selects the entire column B of the spreadsheet

Range(“3:3”).Select <– this selects the entire row 3 of the spreadsheet

Copying and pasting ranges

Two of the useful methods in the Ranges object are Copy and Paste—and they do exactly what you’d expect. Let’s check them out.

First, we’ll use the Copy method to copy a range:

Range("B2:B4").Copy

Then we’ll tell VBA to paste it on another range:

Range("B6").PasteSpecial

(There’s no Paste method for Range, only PasteSpecial.)

vba-range-copy-paste

That’s all there is to it!

There are more complicated ways to copy and paste, such as selecting a range and then using the Copy method on the Selection object. You can also refer to a range and then call the Paste method on the ActiveSheet object.

For now, though, this method will work. And understanding this method of copying and pasting will help you figure out how to solve more complex problems later on.

Clearing cells in a range

Finally, let’s talk about clearing cells. The Clear method will clear everything from a range of cells. That includes values, formats, and comments. Here’s how we’d do that to get rid of the information we just pasted:

Range("B6:B10").Clear

Easy, right?

Head back to the VBA editor, and delete “.Clear”. Start typing it again, and you’ll see that Excel presents you with numerous options via autofill:

vba-clear-options

There are many different types of Clear methods, each clearing a specific type of data: ClearComments, ClearNotes, ClearHyperlinks, and so on.

Another useful one is the ClearContents method, that only clears the values inside the range.

Building on ranges

Even though they’re very basic, ranges form the foundation of many of the things that you’ll do in VBA.

And now that you understand ranges, you’re ready to start moving on to more advanced topics.

2019-10-12T20:55:38+00:00