Finding My

Do you know that tune? It's a song from Rush's debut, self-titled album Rush (July 1974), first track. Good tune, though I appreciate the deeper lyrics that Neil Peart brought to the band with their second album. Anyway, when it comes to working with worksheets, finding your way is a constant theme.

Though most of the time you'll know the general structure of a workbook and worksheet ahead of time, you usually need to feel your way around to get where you are going. Even with highly structured worksheets, it's difficult to predict and account for all of the possible ways end users may use or modify your worksheets.

I certainly don't want to offend anyone with a vision disability by pretending that it's anywhere close to what it is like to be without vision, but that is how I think about navigating worksheets in Excel programmatically when I'm working with documents in which I only have a knowledge of the general structure.

The general technique I use is to feel my way around the document, examining ranges for certain identifying characteristics. These identifying characteristics vary depending on your needs. You may want to look at values; for example, you might contemplate the following questions: Is the cell numeric? Is it within a given range? Or you may consider formatting characteristics; location in the worksheet; location relative to another known range, or a defined name; or the presence of a comment.

Some common tasks you'll need to perform include these:

♦ Moving from cell to cell on a worksheet

♦ Finding the last row in a given column

♦ Determining the last used row for all columns

♦ Detecting the last column for a given row

♦ Determining the last column for all rows

♦ Locating the first empty cell in a column or row

You can apply many different techniques for moving around a worksheet. I'll present a few here that I've used successfully for many different purposes. The two primary ways to move about a worksheet are using the Cells property of the Worksheet object and the Offset property of the Range object. I've already talked about the Cells property earlier in the chapter (see Listing 8.2), so let's take a look at the Offset property.

Offset Is for Relative Navigation

You can use the Offset property of the Range object to refer to ranges on a worksheet based on, or relative to, another range. This property provides you with a great deal of flexibility for moving around a worksheet.

One thing that you can do with Offset is process a structured list. By structured list, I mean a list of items on a worksheet in which you know the order or structure of the columns ahead of time. Consider the list of items shown in Figure 8.8.

One way you could process this list is by setting a reference to the first column of the first row in the list. Then you could loop through the list, advancing your reference down a row, and terminating the loop when you reach an empty row (assuming you know that there won't be any empty rows within the boundaries of your list). As you loop through the list, you could investigate columns of interest by using the Offset property.

Listing 8.6 demonstrates how you could filter this list. You'll have to bear with me and pretend that Excel doesn't have any native filtering functionality (which we'll examine in the next chapter). Anyway, Listing 8.6 uses the Offset property to process the list shown in Figure 8.8 so that it hides any rows that contain cars from the 20th century. Further, this process highlights the mileage column if the car has less than 40,000 miles.

0 0

Post a comment