Function HasData(sh as Worksheet) as Boolean
Dim rng1 as Range, rng2 as Range
HasData = True
On error resume next
set rng1 = sh.cells.specialcells(xlconstants)
set rng2 = sh.cells.specialcells(xlformulas)
On error goto 0
if rng1 is nothing and rng2 is nothing then
HasData = False
elseif not rng1 is nothing then
if rng1.rows.count < 2 then HasData = False
elseif
if rng2.rows.count < 2 then HasData = False
end if
end Function


set sh1 = Activesheet
if not hasdata(sh1) then
exit sub
end if

this assumes you are looking for a data table. It could give a technically
incorrect answer if you had say data in cells A1, B12, A15, Z31 but based on
your description, this should be treated as not having data.

to be specific if rng1 is a multiple area range, then the rows.count is
against the first area.

to illustrate from the immediate window:
set rng = Range("A1,15:50")
? rng.address
$A$1,$15:$50
? rng.rows.count
1

--
Regards,
Tom Ogilvy



"STEVEB" wrote in
message ...

Hi,
Does anyone have any suggestions for the following:
I have a spreadsheet that looks at data and then sorts the data based
on certain text within the cell. The code works great when there are
several rows of data. However, when there is only 1 row of data or no
data for a particular day (This happens once or twice a month) the code
does not work.
Here is an example of the code:

Sub Test()

Range("D2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 =

"=IF(OR(ISNUMBER(SEARCH({""fee"",""inter""},RC[-1]))),""F"",IF(OR(ISNUMBER(S
EARCH({""transf"",""direct
pay"",""xf""},RC[-1]))),""T"",""O""))"
Range("C2").Select
Selection.End(xlDown).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "end"
Selection.End(xlUp).Select
Selection.Copy
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Paste
Range("D2").Select Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial
Paste:=xlValues

Range("A1").Select

Sheets("Sorted").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="T"
Range("A2:d2").Select
Range(Selection, Selection.End(xlDown)).Select Selection.Copy
Sheets("transfers").Select Range("A2").Select ActiveSheet.Paste
Columns("A:d").Select Columns("A:d").EntireColumn.AutoFit
Sheets("Sorted").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=4, Criteria1:="O"
Range("A2:d2").Select
Range(Selection, Selection.End(xlDown)).Select Selection.Copy
Sheets("other").Select Range("A2").Select ActiveSheet.Paste
Columns("A:d").Select Columns("A:d").EntireColumn.AutoFit
Sheets("sorted").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=4, Criteria1:="F"
Range("A2:d2").Select
Range(Selection, Selection.End(xlDown)).Select Selection.Copy
Sheets("Fees-Interest").Select Range("A2").Select ActiveSheet.Paste
Columns("A:d").Select Columns("A:d").EntireColumn.AutoFit
Sheets("sorted").Select
Application.CutCopyMode = False

End Sub

Any Help would be greatly appreciated!!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=509401