Page 452 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects

Microsoft Office Excel 2003 Programming Inside Out
Jumping to the last row is a bit more difficult because the concept of the last row is somewhat
nebulous. After all, just because a worksheet can handle 65,536 rows of data doesn’t mean
that the user of that application wants to view rows that far down. Instead, it makes sense to
look through the worksheet to find the last row with a value in the first column and treat that
as the last row.
To make the last row dynamic, a few changes need to be made to the program. First the
LastRow constant needs to be switched to a variable like this:

Public LastRow As Long
Then the constant needs to be assigned an initial value when the user form is initially loaded.
There are two ways to do this. The easiest way is just to assign it a valid row number such as
3 and then call GetData to load the initial values into the form. So, use the following code to
Chapter 20
create the UserForm_Initialize event.
Private Sub UserForm_Initialize()

GetData

End Sub
If users want to see the last line in the form, they will need to press the Last button. There are
several ways to locate the last row in response to the user clicking the Last button. One way
would be to scan through all of the data looking for the first empty cell in column one each
time the Last button was clicked.

A better way would be to scan through the worksheet and locate the first blank cell in column
1 and assign the value to LastRow, which is what the routine shown in the following listing
does. This routine is located in the user form module.
Private Function FindLastRow()

Dim r As Long

r=2
Do While r < 65536 And Len(Cells(r, 1).Text) > 0
r =r+1
Loop

FindLastRow = r

End Function
The FindLastRow function scans through the worksheet to find the first cell that doesn’t have
a value. A simple While loop iterates through each cell in column one of the worksheet, and
the length of the return value is tested to see if the length is zero. If the length is zero, the loop
will end and r will point to the last row in the worksheet, which is the first blank row follow€
ing the data in the worksheet.

426