Try this:
Dim rowcnt
Err.Clear
On Error Resume Next
rowcnt = Range("MyRange").Rows.Count
If (Err) Then
MsgBox "No More Rows!"
Exit Sub
Else
On Error GoTo 0
End If
Paul
________________________________
From: 0 1 <hhholme...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Tue, December 21, 2010 11:43:12 AM
Subject: $$Excel-Macros$$ Determine if dynamic range contains any rows
I have a macro that deletes rows in a dynamic range if certain data
are missing. When this happens, the range obviously has no rows. I''m
trying to write a macro to determine when this has happened.
Basically:
IF the range has no rows THEN
MsgBox "Range has no rows"
ELSE
MsgBox "Range has rows"
END IF
I'm using the CountA function but it's always incorrect:
Dim countrows As Integer
countrows = Application.WorksheetFunction.CountA("MyRange")
I've tried various calculations, but they always return true or false,
regardless of whether the range has rows
If countrows = 0 Then ...
' always evaluates false, regardless of whether the range has rows
If IsNull(countrows) Then
' always evaluates false, ...
If countrows > 1 Then
' always evaluates false, ...
If countrows = 1 Then
' always evaluates true, ...
If countrows <> 0 Then
' always evaluates true, ...
###
Perhaps CountA isn't the way to go?
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
