If your ranges are named as you show in your example, I would do it like this
(I am assuming that each range begins in column A, so it's first cell is in
column A):

Dim N As Long
Dim p As Long
Dim sTemp As String
Dim myArray(0 to 99) As Variant

p = -1
For N = 1 To 100
sTemp = "Rng" & Format$(N)
If Range(sTemp).Cells(1) < "" Then
p = p + 1
myArray(p) = sTemp
End If
Next N

If p = 0 Then
Redim Preserve myArray(0 To p)
Else
Msgbox "No ranges contain data"
Erase myArray()
End If


On 17 Sep 2005 18:50:52 -0700, "Arishy" wrote:

I have quite a few ranges I need to process. Some of these ranges are
Empty. To save time and memory I need to be "selective" in the line:

myArray=Array(Range("Rng1"),Range("Rng2"),...Rang e("Rng100"))

The Array actually will contain the strings: Rng1, Rng2 , Rng3

How can I "customize" The array line so:

If Column A in Rng3 is Empty I do not include it in the array equation.

A Different approach is to check all ranges first and "dynamically"
build the Array line.

Or program myArray to "delete" some of the entries before I pass it to
the consolidate function. ie Resize the array after deleting some items

Some pointers will be greatly appreciated.