How to Work with VBA Ranges in Excel: Full Guide (2024)

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 the range object.

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

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

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 below code:

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

This tells Excel to look at cell B1.

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.

That’s because referring to a cell and not taking action doesn’t actually do anything.

There’s no property or method involved. Hence it can’t run.

Let’s tell Excel to do something with that cell.

Change your Excel VBA code to this:

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

Now run the macro from the Macros menu again:

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

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

PRO TIP: Objects/properties/methods

The range is an object, but when used together with a property like “.value” it’s often referred to as the “VBA range property”.

Because we’re talking about the Range object, you might be wondering if you can use this sub to refer to multiple cells instead of a single cell. 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!”

You can include multiple VBA range objects like this:

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

And you can even use named ranges:

Range("Dates")

How to refer to cells with the Cells Property

The Cells object is actually a property but it works just like an object does for the sake of what you’ll learn here.

So, the Cells property refers to cells using row and column index numbers instead of regular cell designations. Here’s an example of using the Cells property to manipulate the cell address:

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

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

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:

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.

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:

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:

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

Selecting columns in a range object

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:

Again, remember that the number you use refers to the column within the entire range of the newRange variable, not the worksheet object at large.

If you want to select rows and columns in the entire worksheet, 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 (the destination range):

Range("B6").PasteSpecial

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

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, the above code 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 Excel VBA editor, and delete “.Clear”. Start typing it again, and you’ll see that Excel presents you with numerous options via autofill:

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, which only clears the values inside the range.

That’s it – Now what?

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

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

Click here to enroll in my free 30-minute VBA training.

Other resources

You’re getting the hang of it, right? 👍

If so, read more about how to use VBA in general here.

Also, I mentioned variables earlier in the article. If you’re interested, read up on declaring variables here.

An action often used with the VBA range object is the Offset property. Read all about it here.