Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Detect non-blank range
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 106
Default Detect non-blank range
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,058
Default Detect non-blank range
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 106
Default Detect non-blank range
This works. Thanks.

Gary''s Student wrote:
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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
self-sizing adv.filter criteria range BorisS Excel Discussion (Misc queries) 3 September 24th 06 01:30 PM
Array to named range conversion... i-Zapp Excel Discussion (Misc queries) 4 October 25th 05 09:09 PM
How can I replace a range of blank cells with a 0 Replacing blank cells Excel Discussion (Misc queries) 1 August 19th 05 12:01 AM
Hide columns & rows that contain "0" or blank in a range of cells lw new guest Excel Worksheet Functions 0 August 18th 05 04:27 PM
Lookup with search range start based on position of last blank lin rcmodelr Excel Worksheet Functions 0 November 14th 04 06:32 AM


All times are GMT +1. The time now is 11:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement