Listing Finding the Last Used Cell in a Column or

' returns a range object that represents the last ' non-empty cell in the same column Function GetLastCe11InCo1umn(rg As Range) As Range Dim IMaxRows As Long

IMaxRows = ThisWorkbook.Worksheets(1).Rows.Count

' make sure the last cell in the column is empty If IsEmpty(rg.Parent.Ce11s(1MaxRows, rg.Column)) Then Set GetLastCelllnColumn = _

rg.Parent.Ce11s(1MaxRows, rg.Column).End(xlUp)

Else

Set GetLastCelllnColumn = rg.Parent.Ce11s(1MaxRows, rg.Column) End If End Function

' returns a range object that represents the last ' non-empty cell in the same row Function GetLastCe11InRow(rg As Range) As Range Dim IMaxColumns As Long

IMaxColumns = ThisWorkbook.Worksheets(1).Co1umns.Count

' make sure the last cell in the row is empty If IsEmpty(rg.Parent.Ce11s(rg.Row, IMaxColumns)) Then Set GetLastCellInRow = _

rg.Parent.Ce11s(rg.Row, IMaxColumns).End(xlToLeft)

Else

Set GetLastCellInRow = rg.Parent.Ce11s(rg.Row, IMaxColumns) End If End Function

GetLastCellInColumn and GetLastCellInRow work almost identically, but are different in two ways. First, when you're looking for the last used cell in a column, you need to start at the bottom of the worksheet. For rows, you start at the far right edge of the worksheet instead. The second difference is the parameter you supply to the End property. For the last used cell in a column, you use xlUp; for the last used cell in a row you use xlToLeft. The most important statement in each of these functions is the one that uses the End property. I took the example shown here from the GetLastCellInRow function.

rg.Parent.Ce11s(rg.Row, IMaxColumns).End(xlToLeft)

The rg variable is a Range object supplied to the function as a parameter. Your objective with this statement is to move to the last possible cell in the row to which rg belongs and then use End to move to the first nonempty cell in the same row. You can see exactly how this objective is achieved by breaking the statement down from left to right:

1. First you receive a reference from rg.Parent to the worksheet that contains the range.

2. Next you use the Cells property of the worksheet object to specify the last possible cell in the row. Specify the specific cell by supplying a row number and a column number.

1. You can determine the specific row number by using the Row property of the Range object.

2. You can determine the last possible column by counting the number of columns on a worksheet. This is performed a few statements earlier and the result is assigned to the lMaxColumns variable.

3. Finally, use the End property to find the last nonempty cell in the row.

This is a good example of how you can get a lot done with one statement. As you progress and get more familiar with the Excel object model, putting these statements together will become second nature for you. 99 percent of the time, this statement alone would suffice. In order to account for the possibility that the last possible cell in the row or column isn't empty, you need to add an If.. .Then statement to check for this condition. If the last possible cell is nonempty and you use the End property on that cell, the function returns an incorrect result because it moves off of the cell to find the next nonempty cell.

Listing 8.9 adopts these functions so that they can be called from a worksheet. The main change you need to make is to return a numeric value that can be displayed on a worksheet rather than on a Range object.

0 0

Post a comment