VBA: What is a Range Object?

“Range object” is an unnecessarily intimidating term for a collection of cells within your workbook. Say you need to run some complicated math or formatting over the same cells over and over throughout a long script. You could call on the column number and row every damn time, but who wants to do all that typing?

So how’s it work?

Here’s the most simple example I could think of:

Dim r As Range
Set r = Range("A1:C4")
r.Value = "What are you looking at?"

The result is this:

Now if you are anything like me, you rarely refer to anything by column letter. When I loop through headers, for instance, the value that comes back to me is the cell and column number, not letter.

You can do this, but VBA loves to churn out errors if you don’t format it properly. There are a lot of ways to do this wrong.

See that first thing I tried up there? See how there’s no space after the comma separating the cell values? Yeah, you can’t do that. You need the space, like so:

Dim r As Range
Set r = Range(Cells(1, 1), Cells(5, 5))

r.Value = "What are you looking at?"

Heed the warning. I just spent two days trying to figure out where I’d gone wrong in dozens of lines of code. I missed one space in creating a range object, and it rendered my entire macro useless.

What Can You Do With A Range Object?

A lot. You can select the range. You can even select a specific row or column within that range. You can clear the contents, copy and paste them, go nuts.

Dim r As Range
Set r = Range(Cells(1, 1), Cells(5, 5))
r.Select

The dot notation makes this relatively simple.

r.Rows(1).Select
r.Columns(1).Select
r.ClearContents
r.Copy

Seems straightforward.

THAT’S WHAT IT WANTS YOU TO THINK. Now, say what if you want to make your range size based on variables that are populated at the beginning of the script? Say for instance, you had VBA first check for the last used row and column. Both of those have been thrown into variables. In theory, a script like this could work:

BUT IT DOESN’T. There might be some VBA rule that I’ve overlooked entirely that explains why this refuses to function or maybe it’s just a bug. Because when you change it, ever so slightly, the stupid thing works.

Cells.Select
ActiveWorkbook.Worksheets("NameOfWorksheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("NameOfWorksheet").Sort.SortFields.Add Key:=Range(Cells(1, ColumnToSortBy).Address()), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("NameOfWorksheet").Sort
.SetRange Range((Cells(1, 1).Address()), (Cells(r, n).Address()))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Notice that Address function. That’s one I’ve never had to use before. I spotted it in a thread and threw it into the sort function, now it works like a charm. It returns the absolute reference of the cell. Cells(1, 1) becomes $A$1.

So, that’s about all I can recommend for now if you start running into errors. More VBA next week.