Modify Function to Return Value

[Connie]

I copied the following code from this group to determine the range of
nonblank values on a sheet. It works beautifully. I would like to
modify the function to return not only the range, but also the
RealLastRow. Any help would be appreciated!

Function GetRealLastCell(sh As Worksheet) As Range
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByColumns,
xlPrevious).Column
Set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
End Function

Thanks.

Connie

 

[Bob Phillips]

Function GetRealLastCell(sh As Worksheet, ByRef LastRow As Long, LastCol As
Long) As Range
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByColumns, xlPrevious).Column
Set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
LastRow = RealLastRow
LastCol = RealLastColumn
End Function

Sub TestGetRealLastCell()
Dim i As Long, j As Long
Dim rng As Range

Set rng = GetRealLastCell(ActiveSheet, i, j)
MsgBox "Lastrow = " & i & vbNewLine & "Lastcol = " & j
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[paul.robinson]

Hi
The easiest thing would be to do this in the calling sub;

Set mycell = GetRealLastCell(mysheet)
RealLastrow = mycell.Row

Saves you creating a seperate function.
regards
Paul