dynamic Ranges
Hallo,
How to check if dynamic range is empty (no content)? Thank you for help. Elias |
dynamic Ranges
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 |
dynamic Ranges
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 |
dynamic Ranges
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! |
All times are GMT +1. The time now is 11:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com