Listing Returning the Last Used Cell in a Column or Row with Worksheet Callable Functions

' returns a number that represents the last ' nonempty cell in the same column ' callable from a worksheet Function GetLastUsedRow(rg As Range) As Long

Dim lMaxRows As Long lMaxRows = ThisWorkbook.Worksheets(1).Rows.Count

If IsEmpty(rg.Parent.Cells(lMaxRows, rg.Column)) Then GetLastUsedRow = _

rg.Parent.Cells(lMaxRows, rg.Column).End(xlUp).Row

Else

GetLastUsedRow = rg.Parent.Cells(lMaxRows, rg.Column).Row

End If End Function

' returns a number that represents the last ' nonempty cell in the same row ' callable from a worksheet

Function GetLastUsedColumn(rg As Range) As Long Dim lMaxColumns As Long lMaxColumns = ThisWorkbook.Worksheets(1).Columns.Count

If IsEmpty(rg.Parent.Cells(rg.Row, lMaxColumns)) Then GetLastUsedColumn = _

rg.Parent.Cells(rg.Row, lMaxColumns).End(xlToLeft).Column

Else

GetLastUsedColumn = rg.Parent.Cells(rg.Row, lMaxColumns).Column End If End Function

These functions are handy because you can call them from a worksheet function. Figure 8.11 shows the results of calling these functions from the worksheet you created earlier to experiment with navigation using the Control key in conjunction with the arrow keys.

Figure 8.11

The GetLastUsed-Row and GetLastUsed-Column functions can be called from a worksheet.

Figure 8.11

The GetLastUsed-Row and GetLastUsed-Column functions can be called from a worksheet.

0 0

Post a comment