Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Array Function
I was typing from memory !!!
It should be: myArray = Array("Rng1", "Rng2", "Rng3", "Rng4", "Rng5", "Rng6", "Rng7", " |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array function - I think! | Excel Worksheet Functions | |||
Function for different array | Excel Worksheet Functions | |||
Array Function? | Excel Worksheet Functions | |||
OR function in array-entered IF function | Excel Worksheet Functions | |||
#DIV/0! in the array function | Excel Worksheet Functions |