dynamic Ranges

[adehilis]

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

 

[Guest]

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

 

[Dave Peterson]

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

Maybe???

 

[dehilis ade]

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.