Listing Using the End Property to Navigate within a Worksheet

Sub ExpenmentWithEndO

Dim ws As Worksheet

Dim rg As Range

Set ws = ThisWorkbook.Worksheets(1)

"rg.address = " & rg.Address ws.Cells(2, 8).Value = _

"rg.End(xlDown).Address = " & rg.End(xlDown).Address ws.Cells(3, 8).Value = _

"rg.End(xlDown).End(xlDown).Address = " & _ rg.End(xlDown).End(xlDown).Address ws.Cells(4, 8).Value = _

"rg.End(xlToRight).Address = " & rg.End(xlToRight).Address

Set rg = Nothing

Set ws = Nothing

End Sub

Listing 8.7 simply uses the End property to navigate to a few locations. It outputs the address of the range it navigates to as it goes. Notice that because the End property returns a Range object, you can use it multiple times in the same statement.

As you can see, using End is an efficient technique for finding the boundaries of a contiguous series of cells that contain values. It's also useful for finding the last row in a given column or the last column in a given row. When you use End to find the last used cell in a column or row, however, you need to be wary of empty cells.

In order to account for the possibility of empty cells, all you need to do to find the last cell is start at the boundary of the worksheet. So if you need to find the last row in a given column, use End, except start at the bottom of the worksheet. Similarly, to find the last column in a given row, start at the far right column of the worksheet.

Listing 8.8 presents two functions that return either a range that represents the last used cell in a column or the last used cell in a row, depending on which function you call.

0 0

Post a comment