Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quickly determining whether any values in a range are empty
Hi there,
I am trying to find a way of quickly determining whether any values in a range are empty, preferably without scanning the contents of the cells individually. For instance, it would be ideal to have something like Sheet1.range("A1:A10").value = NullArray where NullArray is an array of Null values of the same length as the number of cells being checked. 1. Is this possible? 2. If not, can someone suggest a better way, say using VBA with a range formula or similar method? I was trying to avoid the expensive alternative of doing something like: NullValue = False numRows = Sheet1.Range("A1:A10").End(xlUp).Row for i in 1 to numRows if len(Sheet1.range("A" & i).value) = 0 then NullValue = True end if next i Any assistance would be appreciated. Regards, Ric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quickly determining whether any values in a range are empty
Hi Ric,
Try something like: Dim rng As Range Set rng = Range("A1:A10") If Application.CountA(rng) < rng.Count Then MsgBox "Empty cells present in range" End If --- Regards, Norman "ric_deez" wrote in message oups.com... Hi there, I am trying to find a way of quickly determining whether any values in a range are empty, preferably without scanning the contents of the cells individually. For instance, it would be ideal to have something like Sheet1.range("A1:A10").value = NullArray where NullArray is an array of Null values of the same length as the number of cells being checked. 1. Is this possible? 2. If not, can someone suggest a better way, say using VBA with a range formula or similar method? I was trying to avoid the expensive alternative of doing something like: NullValue = False numRows = Sheet1.Range("A1:A10").End(xlUp).Row for i in 1 to numRows if len(Sheet1.range("A" & i).value) = 0 then NullValue = True end if next i Any assistance would be appreciated. Regards, Ric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quickly determining whether any values in a range are empty
Hi Norman,
That was concise and clever!!! Thank for your prompt response... Ric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Entering Values and Updating Next Empty Cell in a Range | Excel Worksheet Functions | |||
Determining Excel Formats quickly. | Excel Discussion (Misc queries) | |||
Copy Values to next empty cell range | Excel Worksheet Functions | |||
Cells may look "empty" when quickly scrolling up or down | Excel Discussion (Misc queries) | |||
Determining if values in a column range are of same value. | Excel Programming |