The following will return (as a range) the last real used cell in a worksheet:


Function GetRealLastCell(sh As Worksheet) As Range
Dim r As Range
sh.Activate
For Each r In ActiveSheet.UsedRange
If IsEmpty(r.Value) Then
Else
Set GetRealLastCell = r
End If
Next
End Function


The following illustrates its use:


Sub test()
Worksheets("Sheet2").Range("A1").Value = GetRealLastCell(Sheet1).Address
End Sub
--
Gary's Student


"Connie" wrote:

I am using a function (GetRealLastCell) to detect the range of
non-empty cells on a page of data. If the active worksheet is
"Compiled Totals", I would like to begin the detection on cell A9,
otherwise, cell A1. The reason is that there are header rows on the
sheet "Compiled Totals" and there are some blank lines. Once the range
is detected, I am going to seach the range for certain criteria and set
an autofilter. My function is not returning the correct range.
Following is the code I'm using:


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


Following is my data. The range the function should detect is A9:O34;
however the range the function is detecting is C9:A8. Any help would
be appreciated! Thanks.

10/9/2006 Jonathan
Chavez 23356 1234 5.83 0.00 4.65 1.92 10/9/2006 0.00 0.00 0.00 0.00 12.40 0
10/10/2006 Jonathan
Chavez 23356 1234 0.00 6.42 4.10 0.00 1.92 0.00 0.00 0.00 12.43 0
10/11/2006 Jonathan
Chavez 23356 1234 0.00 0.00 10.42 0.00 0.00 1.92 0.00 0.00 12.33 0
10/12/2006 Jonathan
Chavez 23356 1234 1.92 0.00 4.10 5.70 10/12/2006 0.00 0.00 0.00 0.00 11.72 0
10/13/2006 Jonathan
Chavez 23356 1234 0.00 1.92 4.10 0.00 5.47 0.00 0.00 0.00 11.48 0
10/7/2006 Michael
Albert 23709 1234 7.00 4.00 0.00 0.00 0.00 0.00 0.00 0.00 11.00 20
10/8/2006 Michael
Albert 23709 1234 5.85 0.00 4.10 0.00 0.00 0.00 0.00 0.00 9.95 0
10/9/2006 Michael
Albert 23709 1234 5.83 0.00 4.65 1.92 10/9/2006 0.00 0.00 0.00 0.00 12.40 0
10/10/2006 Michael
Albert 23709 1234 0.00 6.42 4.10 0.00 1.92 0.00 0.00 0.00 12.43 0
10/11/2006 Michael
Albert 23709 1234 0.00 0.00 10.42 0.00 0.00 1.92 0.00 0.00 12.33 0
10/12/2006 Michael
Albert 23709 1234 1.92 0.00 4.10 5.70 10/12/2006 0.00 0.00 0.00 0.00 11.72 0
10/13/2006 Michael
Albert 23709 1234 0.00 1.92 4.10 0.00 5.47 0.00 0.00 0.00 11.48 0
10/7/2006 Daniel
Cappello 24898 2345 7.00 4.00 0.00 0.00 0.00 0.00 0.00 0.00 11.00 20
10/8/2006 Daniel
Cappello 24898 2345 5.85 0.00 4.10 0.00 0.00 0.00 0.00 0.00 9.95 0
10/9/2006 Daniel
Cappello 24898 2345 5.83 0.00 4.65 1.92 10/9/2006 0.00 0.00 0.00 0.00 12.40 0
10/10/2006 Daniel
Cappello 24898 2345 0.00 6.42 4.10 0.00 1.92 0.00 0.00 0.00 12.43 0
10/11/2006 Daniel
Cappello 24898 2345 0.00 0.00 10.42 0.00 0.00 1.92 0.00 0.00 12.33 0
10/12/2006 Daniel
Cappello 24898 2345 1.92 0.00 4.10 5.70 10/12/2006 0.00 0.00 0.00 0.00 11.72 0
10/13/2006 Daniel
Cappello 24898 2345 0.00 1.92 4.10 0.00 5.47 0.00 0.00 0.00 11.48 0
10/7/2006 Kyle
Claymore 29697 2345 7.00 4.00 0.00 0.00 0.00 0.00 0.00 0.00 11.00 20
10/8/2006 Kyle
Claymore 29697 2345 5.85 0.00 4.10 0.00 0.00 0.00 0.00 0.00 9.95 0
10/9/2006 Kyle
Claymore 29697 2345 5.83 0.00 4.65 1.92 10/9/2006 0.00 0.00 0.00 0.00 12.40 0
10/10/2006 Kyle
Claymore 29697 2345 0.00 6.42 4.10 0.00 1.92 0.00 0.00 0.00 12.43 0
10/11/2006 Kyle
Claymore 29697 2345 0.00 0.00 10.42 0.00 0.00 1.92 0.00 0.00 12.33 0
10/12/2006 Kyle
Claymore 29697 2345 1.92 0.00 4.10 5.70 10/12/2006 0.00 0.00 0.00 0.00 11.72 0
10/13/2006 Kyle
Claymore 29697 2345 0.00 1.92 4.10 0.00 5.47 0.00 0.00 0.00 11.48 0




Connie