Modify Function to Return Value

C

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
 
B

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)
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top