VBA Array Function
 

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"),...Range ("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.

VBA Array Function
 

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.

VBA Array Function
 

PS: This line

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

does NOT create an array containing strings like Rng1, Rng2, etc, as text
(which is want you say you want). It contains an array of range objects. That
is not the same thing.

For example, if the array contains text, then to access the data from the
worksheet the code would be

Range(myArray(i)).Cells(j, k)

If myArray contains range objects, you would write

myArray(i).Cells(j, k)

Also, AFAIK, you cannot "'dynamically' build the Array line". That implies
creating self-modifying code, which, IMO, is not a good idea.

The Array(.....) statement normally created when you write the code, not when
the code runs. IOW, you use Array when you know ahead of time exactly what is
to go into the array. If you don't know that until run-time, you don't use
Array. You normally use a "regular" array, whose size is determined at
run-time, as I showed in my previous response.

BTW, I wrote

Dim myArray(0 to 99) As Variant

It should actually be

Dim myArray(0 to 99) As String

if you intend for the array to contain the names of the range as text rather
than range objects.


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.

VBA Array Function
 

Thank you Myrna, for your quick response I will test it on my
Consolidation and let you know. This is really some elegant code you
did... Do appreciate it a lot...

VBA Array Function
 

I was typing from memory !!!

It should be:

myArray = Array("Rng1", "Rng2", "Rng3", "Rng4", "Rng5", "Rng6", "Rng7",
"

VBA Array Function
 

Here is a quick test
Public Sub testConsolidation()
Dim N As Long
Dim p As Long
Dim sTemp As String
Dim myArray(0 To 10) As String

Sheets(2).Range("A1:E8").Name = "Rng1"
Sheets(3).Range("A1:E8").Name = "Rng2"
Sheets(4).Range("A1:E8").Name = "Rng3"
Sheets(5).Range("A1:E8").Name = "Rng4"
Sheets(6).Range("A1:E8").Name = "Rng5"
Sheets(7).Range("A1:E8").Name = "Rng6"
Sheets(8).Range("A1:E8").Name = "Rng7"
Sheets(9).Range("A1:E8").Name = "Rng8"
Sheets(10).Range("A1:E8").Name = "Rng9"


p = -1
For N = 1 To 10
sTemp = "Rng" & Format$(N)
Debug.Print sTemp
If Range(sTemp).Cells(1) < "" Then
p = p + 1
Debug.Print p
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

End Sub

PS It did not like the REDIM line ( says array already defined)

VBA Array Function
 

PS It did not like the REDIM line (says array already defined)

My error. The initial Dim myArray should have been ReDim, or the subscripts
should have been omitted, as below.

I have tested the code below, and it runs without errors.

Sorry for the error and the resulting confusion.

Sub Tested()
Dim N As Long
Dim p As Long
Dim sTemp As String
Dim myArray() As String

ReDim myArray(0 To 99)
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)
For N = 0 To p
Debug.Print myArray(N)
Next N
Else
MsgBox "No ranges contain data"
Erase myArray()
End If
End Sub

VBA Array Function
 

Hi,
I have an excel template that includes all the possible parameters of
projects in that specific field.
The parameters are listed, each in a different row, with 2 columns. One with
the parameter title and the other for its cost.

Now, different projects may use most or all (but not more) parameters in
this list. So i am trying to write a macro that at the end, will scan the
document and find all the rows in column 2 (cost) that has Zero in it and
delete the entire row. Thus leaving behind only the parameters i used.

your help shall be highly appreciated.

"Myrna Larson" wrote:

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.