Worksheet tables: Ranges

Log in

If you're a student, please log in, so you can get the most from this page.

The Range object

A Range object can represent a bunch of cells in a worksheet. Range objects make working with data tables easier. Easy is good.

(You don't need to know what an object is, but there's a page about Excel objects, if you're curious.)

Note

The word "range" has two different meanings. One is the Excel Range object, used in this lesson. The other is the minimum and maximum of a variable. For example, the days in September range from 1 to 30. A "range check" means to validate that a variable is within that range.

Start by downloading messing-with-a-range.xlsm. We'll use it to explore some stuff about ranges.

The workbook has three different worksheets. Choose the first one, "About Ranges". That's what we'll be messing with in this lesson.

Open the file, and you'll see some goat data, and a bunch of buttons.

Worksheet

The fields are name, happiness, and coolness.

Fields

So far, you've only seen one button, attached to one VBA Sub. But you can actually make as many buttons as you want, and as many Subs as you want.

Look at the code, and you'll see:

  • Option Explicit
  •  
  • 'Select the data table.
  • Sub selectRange()
  • ...
  • End Sub
  •  
  • 'Count the records in the data table.
  • Sub countRecords()
  • ...
  • End Sub
  •  
  • 'Count the fields in the data table.
  • Sub countFields()
  • ...
  • End Sub
  •  
  • 'Show a goat's name.
  • Sub showGoatName()
  • ...
  • End Sub
  •  
  • 'Show a goat's happiness.
  • Sub showGoatHappiness()
  • ...
  • End Sub
  •  
  • 'Show a goat's happiness.
  • Sub showGoatHappinessWithConstants()
  • ...
  • End Sub

Each Sub is attached to a different button. Right-click on a button and click Assign Macro, and you'll see of list of Subs you can attach to a button.

Assign Macros lists each Sub

(If you did that, hit Cancel, so you don't change anything.)

Making a Range object

A Range is a bunch of rows and columns in a worksheet. It's like a subset of the worksheet.

You specify a Range by giving its corners. For example, this range...

Range

... goes from Cells(3, 1) to Cells(6, 3).

This Range ...

Another range

... goes from Cells(4, 2) to Cells(13, 4).

Click on the "Select range" button in the sample worksheet. You'll see:

Select a range

Let's check out the first part of the code.

  1. 'Select the data table.
  2. Sub selectRange()
  3.   Dim rGoatDataTable As Range

Line 3 adds a new data type. Remember that we have Single, and String. Now, we have a new type of thing, a Range.

Here's the code again:

  1. 'Select the data table.
  2. Sub selectRange()
  3.   Dim rGoatDataTable As Range
  4.   'Set up the Range object.
  5.   Set rGoatDataTable = Range(Cells(3, 1), Cells(3, 1).End(xlToRight).End(xlDown))
  6.   'Select all of the records and fields.
  7.   rGoatDataTable.Select
  8. End Sub

You need to create a Range object before you can do anything with it. That's what line 5 does:

Set rGoatDataTable = Range(Cells(3, 1), Cells(3, 1).End(xlToRight).End(xlDown))

It's an assignment statement, so, the thing on the right gets put into the thing on the left. But with objects, you have to add Set in front of it. Because... well, don't worry about why, just do it.

Let's look at the Range function call:

Range(Cells(3, 1), Cells(3, 1).End(xlToRight).End(xlDown))

What does the mean?

Remember, a Range is a rectangular block of cells. Like this:

Select a range

The way you specify a range is to tell VBA what the top left cell is, and what the bottom right cell is. (There are other ways, too, but let's stick with this.)

So it's:

  • Range(top left cell, bottom right cell)

The top left cell of the data table is row 3, column 1. So:

  • Range(Cells(3, 1), bottom right cell)

OK. The bottom right cell is 15, 3. So we could do this.

  • Range(Cells(3, 1), Cells(15, 3))

There's a problem, though. The number of goats changes every day. Today, it's 13. Tomorrow, it's 128. Next year, it's 65,536. How to write code that works, no matter how big the data table is? The starting cell will always be 3, 1. But the ending cell will be changing all the time!

The trick is to simulate what you do when you use Excel with the keyboard. Not everyone knows it, but you can select a data table with just a few keystrokes. Click in cell 3,1.

Upper left cell

Now, Ctrl+Shift+↓. That is, hold down control and shift, and press the down arrow. Excel will select all of the data downward, until it finds an empty row.

Select down

Next, Ctrl+Shift+→. That's control, shift, right arrow. Excel will select cells to the right, until it runs out of data.

Select right

Woohoo! So that's:

  • Click in the upper left cell of the range.
  • Ctrl+Shift+↓
  • Ctrl+Shift+→

We can make Excel act the same way in code. This...

Cells(3, 1).End(xlDown).End(xlToRight)

... says "Start in cell 3,1, then go to the end cell (the last one with data) in the down direction, then go to the end cell in the right direction."

We can use this to get to the last field of the last record, no matter how many records there are. 13? 128? 65,536? No worries. Cells(3, 1).End(xlDown).End(xlToRight) will handle them all.

Here's the line of the code that makes the Range object.

Set rGoatDataTable = Range(Cells(3, 1), Cells(3, 1).End(xlToRight).End(xlDown))

This says: "Make a Range object, with the upper left at 3,1, and the lower right at wherever you end up when you start at 3,1, then go to the right until you run out of data, and down until you run out of data."

(Notice that I was going down and then right, and this one goes right and then down. Doesn't matter. Same result.)

Pattern

Create a Range

Set rSomething = Range(top left cell, bottom right cell)

Here's the code again:

  1. 'Select the data table.
  2. Sub selectRange()
  3.   Dim rGoatDataTable As Range
  4.   'Set up the Range object.
  5.   Set rGoatDataTable = Range(Cells(3, 1), Cells(3, 1).End(xlToRight).End(xlDown))
  6.   'Select all of the records and fields.
  7.   rGoatDataTable.Select
  8. End Sub

So we make a Range object corresponding to our data table, and then:

rGoatDataTable.Select

That just selects it. So we see:

Select a range

Try it. Download the worksheet if you haven't already.

Now, a question for you.

Reflect

Suppose you replace a row with emptiness, like this:

Data table with one row removed from the middle

Predict what will happen when you click the "Select range" button. Type that in.

Now click the button. Was your prediction right?

(If you were logged in as a student, the lesson would pause here, and you'd be asked to type in a response. If you want to try that out, ask for an account on this site.)
Ray
Ray

I thought it would select only above the missing row, and that's what happened.

Excel selected up to the missing row

Right. Start at cell 3, 1, select right and down until you come to some empty cells. That's what happened.

Put the row back in again. Undo (Ctrl+Z) is the easiest way.

Number of records and fields

You'll often need to know the number of records in your Range. Here's some code:

  1. 'Count the records in the data table.
  2. Sub countRecords()
  3.     Dim rGoatDataTable As Range
  4.     Dim sNumberOfRecordsInRange As Single
  5.     'Set up the Range object.
  6.     Set rGoatDataTable = Range(Cells(3, 1), Cells(3, 1).End(xlToRight).End(xlDown))
  7.     'Get the number of rows in the range. We use one row for each record.
  8.     sNumberOfRecordsInRange = rGoatDataTable.Rows.Count
  9.     'Show the number of records.
  10.     MsgBox "There are " & sNumberOfRecordsInRange & " records."
  11. End Sub

Line 6 sets up the Range, as usual. Line 8 asks the Range how many rows it has. We're using one row per record, so the row count is also the record count.

You can do the same kind of thing to get the number of fields.

  1. 'Count the fields in the data table.
  2. Sub countFields()
  3.     Dim rGoatDataTable As Range
  4.     Dim sNumberOfFields As Single
  5.     'Set up the Range object.
  6.     Set rGoatDataTable = Range(Cells(3, 1), Cells(3, 1).End(xlToRight).End(xlDown))
  7.     'Get the number of columns in the range. We use one column for each field.
  8.     sNumberOfFields = rGoatDataTable.Columns.Count
  9.     'Show the number of fields.
  10.     MsgBox "There are " & sNumberOfFields & " fields."
  11. End Sub

Line 8 asks the Range how many columns it has. We're using one column per field, so the column count is also the field count.

Access data in the Range

So far in the course, we've been using Cells() to get data from a cell in the worksheet. So Cells(2, 2) gets the data in cell B2 of the worksheet.

You can also use Cells() to get data in a Range. Here's the data again.

Worksheet

So...

  • rGoatRange.Cells(3, 2)

... means row 3, column 2 in the Range. That's 6.

Ray
Ray

Wait, what? Cells(3, 2) is 5.

Cell 3, 2

Cells(3, 2) is 5, but rGoatRange.Cells(3, 2) is 6.

rGoatRange cell 3, 2

Ray
Ray

Oh, OK. rGoatRange.Cells(3, 2) is, like, starting from the top left of wherever rGoatRange starts. Not from the top left of the entire worksheet.

Right!

Fill in the blank

What's the value in the Range?

With this:

Worksheet

What's the value of...

  • goatDataTable.Cells(5, 3)
Your answer:
Saving

Try the "Show Happiness" button in the worksheet. You give it a record number, and it shows you a value:

Entering value

Here's the code for the button:

  1. 'Show a goat's happiness.
  2. Sub showGoatHappiness()
  3.     Dim rGoatDataTable As Range
  4.     Dim sRecordNumberToShow As Single
  5.     Dim sNumberOfRecordsInRange As Single
  6.     Dim tGoatName As String
  7.     Dim sGoatHappiness As Single
  8.  
  9.     'Set up the Range object.
  10.     Set rGoatDataTable = Range(Cells(3, 1), Cells(3, 1).End(xlToRight).End(xlDown))
  11.     'Ask user for the number of the record they want to check.
  12.     sRecordNumberToShow = InputBox("Record number?")
  13.     'Validate.
  14.     sNumberOfRecordsInRange = rGoatDataTable.Rows.Count
  15.     If sRecordNumberToShow > sNumberOfRecordsInRange Then
  16.         MsgBox "Sorry, we only have " & sNumberOfRecordsInRange & " records."
  17.         End
  18.     End If
  19.  
  20.     'Get the goat's name.
  21.     tGoatName = rGoatDataTable.Cells(sRecordNumberToShow, 1)
  22.     'Get the goat happiness.
  23.     sGoatHappiness = rGoatDataTable.Cells(sRecordNumberToShow, 2)
  24.     'Output.
  25.     MsgBox tGoatName & "'s happiness is " & sGoatHappiness & "."
  26. End Sub

Line 10 sets up the Range as usual.

Then we ask the user what record number they want.

  • 'Ask user for the number of the record they want to check.
  • sRecordNumberToShow = InputBox("Record number?")

They could have asked for a record we don't have, like record 66, when we only have 13 records. The next few lines validate the user's input.

  • 'Validate.
  • sNumberOfRecordsInRange = rGoatDataTable.Rows.Count
  • If sRecordNumberToShow > sNumberOfRecordsInRange Then
  •     MsgBox "Sorry, we only have " & sNumberOfRecordsInRange & " records."
  •     End
  • End If

We get the number of records in the Range, using rGoatDataTable.Rows.Count. If the user asks for a record beyond that, show an error message.

Then we get the data from the record the user wanted.

  • 'Get the goat's name.
  • tGoatName = rGoatDataTable.Cells(sRecordNumberToShow, 1)
  • 'Get the goat happiness.
  • sGoatHappiness = rGoatDataTable.Cells(sRecordNumberToShow, 2)

Finally, show the output to the user.

  • 'Output.
  • MsgBox tGoatName & "'s happiness is " & sGoatHappiness & "."

Comments for variables

You can add comments when you declare variables, to make it easier to understand what they mean. Put a comment above the Dim for the variable the comment is about. Like this:

  • 'Show a goat's happiness.
  • Sub showGoatHappiness()
  •     'Goat records in the worksheet.
  •     Dim rGoatDataTable As Range
  •     'The position of one record in the range. E.g., 11 means the 11th record.
  •     Dim sCurrentRecordNumber As Single
  •     'The number of records in the range, e.g., 32 means there are 32 records.
  •     Dim sNumberOfRecordsInRange As Single

Notice that both of the comments have examples. Adding an example in a comment can really help.

Using constants for column numbers

In the business world, the data that you're analyzing could come from anywhere. Internal data, a service your company subscribes to, a public data set on the web... lots of places.

It's common for the format of data sets to change. For example, the data set we have here has the fields name, happiness, and coolness:

Fields

Maybe whomever supplies us with the data set adds another column, age. They add it after name.

New field after name

Now we have more data to play with. Hooray!

Except, now some of our code is brokeh. Here's what we have in the get-happiness code:

  • 'Get the goat happiness.
  • sGoatHappiness = rGoatDataTable.Cells(sCurrentRecordNumber, 2)

Oh, spit! Happiness is now field 3, not field 2. Not a big deal to change it, just for this program. But a typical data set will have dozens of fields. We have to change the code for many of them. Coolness is now field 4 instead of field 3, and so on.

It would be easy to miss one of the field numbers, or type it in wrongly.

Wouldn't it be nice if we could code something like this instead?

  • 'Get the goat happiness.
  • sGoatHappiness = rGoatDataTable.Cells(sCurrentRecordNumber, HAPPINESS_FIELD)

Instead of 2 for the field, we type a name instead, like HAPPINESS_FIELD.

Wouldn't that be cool?

Goat

Sure would.

That's easy to set up in VBA, with things called Consts. A Const is a name you give to a constant, or a fixed value. You declare them along with variables, like this:

  • 'Show a goat's happiness.
  • Sub showGoatHappinessWithConstants()
  •     Dim rGoatDataTable As Range
  •     Dim sCurrentRecordNumber As Single
  •     Dim sNumberOfRecordsInRange As Single
  •     Dim tGoatName As String
  •     Dim sGoatHappiness As Single
  •  
  •     Const NAME_FIELD = 1
  •     Const HAPPINESS_FIELD = 2
  •     Const COOLNESS_FIELD = 3

Whenever you use NAME_FIELD in your code, VBA replaces it with 1. When you use HAPPINESS_FIELD VBA replaces it with 2. And so on. So:

  • 'Get the goat's name.
  • tGoatName = rGoatDataTable.Cells(sCurrentRecordNumber, NAME_FIELD)
  • 'Get the goat happiness.
  • sGoatHappiness = rGoatDataTable.Cells(sCurrentRecordNumber, HAPPINESS_FIELD)

What happens when the age field is added? You change the Consts, and leave the rest of the code alone:

  • 'Show a goat's happiness.
  • Sub showGoatHappinessWithConstants()
  •     ...
  •     Const NAME_FIELD = 1
  •     Const NAME_AGE = 2
  •     Const HAPPINESS_FIELD = 3
  •     Const COOLNESS_FIELD = 4

Changes are all in one place. They're easy to check, and test.

Module-level Consts

There's still an issue, though. Remember that this worksheet has six buttons, with six Subs.

  • Option Explicit
  •  
  • 'Select the data table.
  • Sub selectRange()
  • ...
  • End Sub
  •  
  • 'Count the records in the data table.
  • Sub countRecords()
  • ...
  • End Sub
  •  
  • 'Count the fields in the data table.
  • Sub countFields()
  • ...
  • End Sub
  •  
  • 'Show a goat's name.
  • Sub showGoatName()
  • ...
  • End Sub
  •  
  • 'Show a goat's happiness.
  • Sub showGoatHappiness()
  • ...
  • End Sub
  •  
  • 'Show a goat's happiness.
  • Sub showGoatHappinessWithConstants()
  • ...
  • End Sub

We could have the same Consts in each Sub:

  • Option Explicit
  •  
  • 'Select the data table.
  • Sub selectRange()
  •     ...
  •     Const NAME_FIELD = 1
  •     Const HAPPINESS_FIELD = 2
  •     Const COOLNESS_FIELD = 3
  •     ...
  • End Sub
  •  
  • 'Count the records in the data table.
  • Sub countRecords()
  •     ...
  •     Const NAME_FIELD = 1
  •     Const HAPPINESS_FIELD = 2
  •     Const COOLNESS_FIELD = 3
  •     ...
  • End Sub
  •  
  • 'Count the fields in the data table.
  • Sub countFields()
  •     ...
  •     Const NAME_FIELD = 1
  •     Const HAPPINESS_FIELD = 2
  •     Const COOLNESS_FIELD = 3
  •     ...
  • End Sub
  •  
  • 'Show a goat's name.
  • Sub showGoatName()
  •     ...
  •     Const NAME_FIELD = 1
  •     Const HAPPINESS_FIELD = 2
  •     Const COOLNESS_FIELD = 3
  •     ...
  • End Sub
  •  
  • 'Show a goat's happiness.
  • Sub showGoatHappiness()
  •     ...
  •     Const NAME_FIELD = 1
  •     Const HAPPINESS_FIELD = 2
  •     Const COOLNESS_FIELD = 3
  •     ...
  • End Sub
  •  
  • 'Show a goat's happiness.
  • Sub showGoatHappinessWithConstants()
  •     ...
  •     Const NAME_FIELD = 1
  •     Const HAPPINESS_FIELD = 2
  •     Const COOLNESS_FIELD = 3
  •     ...
  • End Sub

If we need to add the age field, we'd need to change a lot of code. Ack!

There's a solution, though. We could move the Consts outside of the Subs:

  • Option Explicit
  •  
  • 'Consts for field numbers.
  • Const NAME_FIELD = 1
  • Const HAPPINESS_FIELD = 2
  • Const COOLNESS_FIELD = 3
  •  
  • 'Select the data table.
  • Sub selectRange()
  • ...

We can use the Consts in any Sub, as before. But now we have them defined just once. Easy to change. W00t!

The Consts are said to be declared at the module level. Module-level things are available to all Subs. Things declared inside a Sub (as we have been doing in the course so far) are only available in that Sub. They are local to the Sub.

Georgina
Georgina

Oo! Oo! You took Consts outside of the Subs, moved them to the top of the code. Now they're shared by all of the Subs.

Can you take variables outside of the Subs, and move them to the top of the code? They'd be shared by all of the Subs.

Yes! Good thinking, Georgina! We can do that. Something like:

  • Option Explicit
  •  
  • 'Consts for field numbers.
  • Const NAME_FIELD = 1
  • Const HAPPINESS_FIELD = 2
  • Const COOLNESS_FIELD = 3
  •  
  • Dim rGoatDataTable As Range
  •  
  • 'Select the data table.
  • Sub selectRange()
  • ...

Now rGoatDataTable is declared just once, and all Subs use it.

Let's not worry about module-level variables right now, though. Let's just do Consts at the module level. There's enough new stuff to fill our brains.

Summary

  • A Range is an area in a worksheet.
  • Ranges are good for working with data tables (records with fields).
  • Make a Range with Set rDataTable = Range(...).
  • Get the number of records in a data table: rDataTable.Rows.Count
  • Get the number of fields in a data table: rDataTable.Columns.Count
  • Access a value in a data table: Cells(33, 3).
  • Define Consts, and you can use them for the field number: Cells(33, FIELD_NAME).
  • Define Consts at the module level, and all Subs in the module can use them.

Exercise

Exercise

Favorite leaf

Some goats have taken a taste test, saying how much they like three different leafs. Download a start worksheet that has the data.

It looks like this:

Data

When the user clicks on the button, the program asks for a record number, like this:

Ask for record

It grabs that record, and shows the name of the goat, and it's fave leaf:

Output

Notice that the output is on two separate lines.

Validate the record number the user types. It should be number that's one or more, but not greater than the number of records. For example, if someone typed...

Invalid input: non-numeric

... they would see...

Error message for non-numeric input

If they typed a number what was out of range...

Record number too high

... they would see...

Output when record number is too high

Write the code so that the number of records could change, as more data is gathered. That is, don't hard-wire your code so that it only works for 50 goats. So, if data for 20 more goats was added, your program would accept record numbers up to 70, without any of your code changing.

Here's another output example, showing what happens when a goat likes two leaves equally, like record 4:

Likes two leaves equally

Here are the output possibilities.

Sitch Message
A, B, and C are equal Likes all types equally
A is highest Type A
B is highest Type B
C is highest Type C
A = B, and both are higher than C A and B are equal faves
A = C, and both are higher than B A and C are equal faves
B = C, and both are higher than A B and C are equal faves

Upload your workbook. The usual coding standards apply.

Up next

Now you know how to create a Range object, and access its properties, like Rows.Count. The next step: processing all of the data in a Range, with a loop.