Listing List Processing with the Offset Property

Sub ListExample()

FilterYear 2000 End Sub

Sub Reset()

With ThisWorkbook.Worksheets("List Example") .Rows.Hidden = False .Rows.Font.Bold = False .Rows(1).Font.Bold = True End With End Sub

Sub FilterYear(nYear As Integer) Dim rg As Range Dim nMileageOffset As Integer

' 1st row is column header so start ' with 2nd row

Set rg = ThisWorkbook.Worksheets("List Example").Range("A2") nMileageOffset = 6

' go until we bump into first ' empty cell Do Until IsEmpty(rg)

If rg.Value < nYear Then rg.EntireRow.Hidden = True

Else

' check milage

If rg.Offset(0, nMileageOffset).Value < 40000 Then rg.Offset(0, nMileageOffset).Font.Bold = True

Else rg.Offset(0, nMileageOffset).Font.Bold = False End If rg.EntireRow.Hidden = False End If

' move down to the next row Set rg = rg.Offset(1, 0)

Loop

Set rg = Nothing End Sub

Let me make a few comments before I analyze this listing. First, this listing uses a worksheet named "List Example" and doesn't validate this assumption, so be sure you have either changed the worksheet name in the code or named one of your worksheets "List Example". Then run the ListExample procedure to hide selected rows and the Reset procedure to display the worksheet as it originally appeared.

The heart of this example is the poorly named FilterYear procedure. Notice one of the variables is named nMileageOffset. The procedure uses the Offset property to observe the value in the Mileage column. Your Range object variable, rg, is located in column 1, so to use Offset to view the Mileage column, you need to look in the cell six columns to the right. It's a good idea, at a minimum, to store a value like this in a variable or a constant. That way if you need to change the value (perhaps you need to insert a column, for example), you only have to change it in one location. The FilterYear stores this mileage column offset in a variable named nMileageOffset.

The processing loop is a Do.. .Loop that terminates when it finds an empty cell. Each time through the loop, you set the rg variable to refer to the next cell down. The primary assumption that is made here is that you don't have any empty cells between the first and last row. If there is a chance that you may have empty cells, you need to use another method to process the list or put some appropriate checks in place.

The first piece of business inside the loop is to see if the value in the year column (the value in the range to which the rg variable refers) is less than the value passed in the nYear parameter. If it is, you can use the EntireRow property of your Range object to refer to a range that represents all of the cells in the row occupied by the rg variable. In the same statement, set the Hidden property to true to hide the row. If the value in the Year column is equal to or greater than nYear, check the value in the Mileage column and ensure that the row isn't hidden. The last thing to do inside the loop is advance the rg variable so that it refers to the next cell down the worksheet.

Notice in the Reset procedure that you can return a range that represents all of the rows in a worksheet or a selected row in the worksheet. This makes it very easy to unhide all of the rows at once. In order to remove any bold formatting that the FilterYear procedure applied, remove any bold formatting from any cell on the worksheet and then go back and apply it to the first row (the column headings). This is much easier and faster than looping through each cell individually and turning bold off if it is on. The output of Listing 8.6 is shown in Figure 8.9.

Figure 8.9

List processing can be performed very easily with just a little bit of code.

Figure 8.9

List processing can be performed very easily with just a little bit of code.

E3 Microsoft Office Excel Z003 Beta - Chapter 8 Examples.xls

HHH

I:®] Eile Edit tfew

Insert Fermât Jpols [£<sta Window Help

i Type a quesScn For hdp » _ 51

Uir

1^10 - |ïj/

Il S 3 ÉS 1 % » ÎÈ ¿a | SJF _ - & - A -

J

U

®J - MM as Sffiti *

A14

-

*

A

■ i

c 1

D B F

S

H

<

1

Vs.ir

M.iks

Make i;1

Color ! IIij ii>' • I|i 11■ ■;i

Mileage

Plies

3

2001

ACURA

32 CL

GOLD 6C Gas CD. EW. Lthr. PM

30.515

18,150

4

2000

ACURA

3.2 TL

SILVER 6C Gas CD. EW. Lthr. 5R

43.466

17.1500

11

2002

BMW

ÏS3.0L

GRAY GC Gas CD, EW, Lthr, PM

31,275

36,5/5

12

20D1

BMW

325X1

WHITE 6C Gas CD, EW, Lthr, PM

36.380

22,440

! 14

;—i

15

16

17

V

18

19

20

21

22

Im < > Hl\ 5hett] / 5h&3t2 \ListE*drnij(e / l<

.1

> 1

| Ba.sJ;.-

0 0

Post a comment