Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use IsEmpty on a range without a loop? XL03
IsEmpty addresses a single variable; IsEmpty(MyVar) determines if it has been
intialized. I have a named range which is 12 cells. I want to identify when the first cell has data but the other 11 cells are 'empty'. I've simplified the code sample below, but I realize that it will be a 2-D array and which parameter matters will depend on whether the data is in columns or rows... I'm just trying to see if this is even possible. My current option is to loop: MyAbortCode = False for i = 2 to 12 if IsEmpty(MyArray(i))=False then MyAbortCode = True Next If MyAbortCode = False then 'do stuff End If but I'm wondering if there is any way to do this all at once without a loop. If IsEmpty(MyArray(2 to 12))= true then 'do stuff End If Thank you for any ideas, Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use IsEmpty on a range without a loop? XL03
How about this?
If Application.CountA(MyArray) 1 Then MsgBox "More than 1" End If HTH Bob "ker_01" wrote in message ... IsEmpty addresses a single variable; IsEmpty(MyVar) determines if it has been intialized. I have a named range which is 12 cells. I want to identify when the first cell has data but the other 11 cells are 'empty'. I've simplified the code sample below, but I realize that it will be a 2-D array and which parameter matters will depend on whether the data is in columns or rows... I'm just trying to see if this is even possible. My current option is to loop: MyAbortCode = False for i = 2 to 12 if IsEmpty(MyArray(i))=False then MyAbortCode = True Next If MyAbortCode = False then 'do stuff End If but I'm wondering if there is any way to do this all at once without a loop. If IsEmpty(MyArray(2 to 12))= true then 'do stuff End If Thank you for any ideas, Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use IsEmpty on a range without a loop? XL03
If you're looking at a contiguous range, you could use:
with worksheets("Sheet999").range("SomeNameHere") if application.count(.cells) = 0 then 'all cells are empty else 'not all cells are empty end if End with If you wanted to see if all were filled, you could use: with worksheets("Sheet999").range("SomeNameHere") if application.count(.cells) = .cells.count then 'all cells are filled else 'not all cells are filled end if End with ker_01 wrote: IsEmpty addresses a single variable; IsEmpty(MyVar) determines if it has been intialized. I have a named range which is 12 cells. I want to identify when the first cell has data but the other 11 cells are 'empty'. I've simplified the code sample below, but I realize that it will be a 2-D array and which parameter matters will depend on whether the data is in columns or rows... I'm just trying to see if this is even possible. My current option is to loop: MyAbortCode = False for i = 2 to 12 if IsEmpty(MyArray(i))=False then MyAbortCode = True Next If MyAbortCode = False then 'do stuff End If but I'm wondering if there is any way to do this all at once without a loop. If IsEmpty(MyArray(2 to 12))= true then 'do stuff End If Thank you for any ideas, Keith -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use IsEmpty on a range without a loop? XL03
That did help me realize that the sample range I was working with was not the
one I thought :) I have formulas in some source ranges (like the one I was working with) that throws off the IsEmpty and CountA, but .Count will be perfect (I think- I still need to do more testing). Thank you Bob! Keith "Bob Phillips" wrote: How about this? If Application.CountA(MyArray) 1 Then MsgBox "More than 1" End If HTH Bob "ker_01" wrote in message ... IsEmpty addresses a single variable; IsEmpty(MyVar) determines if it has been intialized. I have a named range which is 12 cells. I want to identify when the first cell has data but the other 11 cells are 'empty'. I've simplified the code sample below, but I realize that it will be a 2-D array and which parameter matters will depend on whether the data is in columns or rows... I'm just trying to see if this is even possible. My current option is to loop: MyAbortCode = False for i = 2 to 12 if IsEmpty(MyArray(i))=False then MyAbortCode = True Next If MyAbortCode = False then 'do stuff End If but I'm wondering if there is any way to do this all at once without a loop. If IsEmpty(MyArray(2 to 12))= true then 'do stuff End If Thank you for any ideas, Keith . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation drop-down width, with named range source (XL03 and | Excel Worksheet Functions | |||
Loop Statement through If Not IsEmpty Then Paste into Destination | Excel Discussion (Misc queries) | |||
Can you do IsEmpty(Range)? | Excel Programming | |||
IsEmpty on a range/array | Excel Programming | |||
Do.....Loop until IsEmpty(....) | Excel Programming |