dynamic Ranges

Hallo,
How to check if dynamic range is empty (no content)?
Thank you for help.
Elias

is the ISEMPTY function?

Sub TestFunction()
MsgBox CheckEmpty("MyData")
End Sub

Function CheckEmpty(sRangeName As String) As Boolean
Dim cell As Range
CheckEmpty = True
For Each cell In Range(sRangeName).Cells
If Not IsEmpty(cell) Then
CheckEmpty = False
Exit For
End If
Next

End Function


I'm pretty sure that there's a better way
....aha !

Function CheckEmpty(sRangeName As String) As Boolean
CheckEmpty = WorksheetFunction.CountA(Range(sRangeName)) = 0
End Function

om a sheet name a range like "MyData"
in another cell
=COUNTA(MyData)=0
....will be TRUE if there's nothing in the range

Patrick Molloy
Microsoft Excel MVP

"adehilis" wrote:

> Hallo,
> How to check if dynamic range is empty (no content)?
> Thank you for help.
> Elias
>
>
>

If application.counta(sRangeName) = 0 then
'it's empty
else
'it's not
end if

Maybe???

Patrick Molloy wrote:
>
> is the ISEMPTY function?
>
> Sub TestFunction()
> MsgBox CheckEmpty("MyData")
> End Sub
>
> Function CheckEmpty(sRangeName As String) As Boolean
> Dim cell As Range
> CheckEmpty = True
> For Each cell In Range(sRangeName).Cells
> If Not IsEmpty(cell) Then
> CheckEmpty = False
> Exit For
> End If
> Next
>
> End Function
>
> I'm pretty sure that there's a better way
> ...aha !
>
> Function CheckEmpty(sRangeName As String) As Boolean
> CheckEmpty = WorksheetFunction.CountA(Range(sRangeName)) = 0
> End Function
>
> om a sheet name a range like "MyData"
> in another cell
> =COUNTA(MyData)=0
> ...will be TRUE if there's nothing in the range
>
> Patrick Molloy
> Microsoft Excel MVP
>
> "adehilis" wrote:
>
> > Hallo,
> > How to check if dynamic range is empty (no content)?
> > Thank you for help.
> > Elias
> >
> >
> >

--

Dave Peterson

Thank you a lot for reply ,
i used it it works ,thanks again.
I thought that there are in vba spcial methode to handel range.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!