Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify Function to Return Value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify Function to Return Value
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) "Connie" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify Function to Return Value
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 Connie wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify search function | Excel Worksheet Functions | |||
How a function can modify a value from some cells ? | Excel Programming | |||
Can I modify a Hyperlink with a function? | Excel Discussion (Misc queries) | |||
Modify cell from function | Excel Programming | |||
to modify cells from a function | Excel Programming |