Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReDim Array
I have some VBA code that looks at a data table on a worksheet. The code
decides whether or not a given line meets certain criteria or not and if it does it records the row into the next open row of an array. I do not know how many rows of good data will be found so I have set a constant Selections = 50 and then dimensioned the array to: Dim Array_Temp(1 to Selections, 1 to 5). There will however probably only be 10 to 20 used entries. Once the array has been successfully filled I would like to re-dimension it (or something) to remove the rows that were not needed so that I can send it to a sorting function and not have the rows of non used entries. Thanks for any help Brandt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReDim Array
Brandt, here is a really good site on the use of static and dynamic arrays:
http://www.exceltip.com/st/Array_var...Excel/509.html Post back if you need more help -- Charles Chickering "A good example is twice the value of good advice." "Brandt" wrote: I have some VBA code that looks at a data table on a worksheet. The code decides whether or not a given line meets certain criteria or not and if it does it records the row into the next open row of an array. I do not know how many rows of good data will be found so I have set a constant Selections = 50 and then dimensioned the array to: Dim Array_Temp(1 to Selections, 1 to 5). There will however probably only be 10 to 20 used entries. Once the array has been successfully filled I would like to re-dimension it (or something) to remove the rows that were not needed so that I can send it to a sorting function and not have the rows of non used entries. Thanks for any help Brandt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReDim Array
You can only redim the last dimension of a dynamic array. To accomplish what
you want, you would need to transpose, redim and transpose back Sub Tester1() Dim Array_Temp() Const Selections As Long = 50 ReDim Array_Temp(1 To Selections, 1 To 5) ' code to fill up the array For i = 1 To Int(Rnd() * 50 + 1) For j = 1 To 5 Array_Temp(i, j) = Int(Rnd() * 100 + 1) Next Next maxUsed = 0 For i = 1 To Selections If Not IsEmpty(Array_Temp(i, 1)) Then maxUsed = i End If Next Debug.Print "Befo ", maxUsed, UBound(Array_Temp, 1) Array_Temp = Application.Transpose(Array_Temp) ReDim Preserve Array_Temp(1 To 5, 1 To maxUsed) Array_Temp = Application.Transpose(Array_Temp) Debug.Print "After: ", maxUsed, UBound(Array_Temp, 1) End Sub this produced: Befo 36 50 After: 36 36 as an example. -- Regards, Tom Ogilvy "Brandt" wrote: I have some VBA code that looks at a data table on a worksheet. The code decides whether or not a given line meets certain criteria or not and if it does it records the row into the next open row of an array. I do not know how many rows of good data will be found so I have set a constant Selections = 50 and then dimensioned the array to: Dim Array_Temp(1 to Selections, 1 to 5). There will however probably only be 10 to 20 used entries. Once the array has been successfully filled I would like to re-dimension it (or something) to remove the rows that were not needed so that I can send it to a sorting function and not have the rows of non used entries. Thanks for any help Brandt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReDim Array
Thank You Tom and Charles
I think Tom's Code is Exactly what I need Brandt "Tom Ogilvy" wrote: You can only redim the last dimension of a dynamic array. To accomplish what you want, you would need to transpose, redim and transpose back Sub Tester1() Dim Array_Temp() Const Selections As Long = 50 ReDim Array_Temp(1 To Selections, 1 To 5) ' code to fill up the array For i = 1 To Int(Rnd() * 50 + 1) For j = 1 To 5 Array_Temp(i, j) = Int(Rnd() * 100 + 1) Next Next maxUsed = 0 For i = 1 To Selections If Not IsEmpty(Array_Temp(i, 1)) Then maxUsed = i End If Next Debug.Print "Befo ", maxUsed, UBound(Array_Temp, 1) Array_Temp = Application.Transpose(Array_Temp) ReDim Preserve Array_Temp(1 To 5, 1 To maxUsed) Array_Temp = Application.Transpose(Array_Temp) Debug.Print "After: ", maxUsed, UBound(Array_Temp, 1) End Sub this produced: Befo 36 50 After: 36 36 as an example. -- Regards, Tom Ogilvy "Brandt" wrote: I have some VBA code that looks at a data table on a worksheet. The code decides whether or not a given line meets certain criteria or not and if it does it records the row into the next open row of an array. I do not know how many rows of good data will be found so I have set a constant Selections = 50 and then dimensioned the array to: Dim Array_Temp(1 to Selections, 1 to 5). There will however probably only be 10 to 20 used entries. Once the array has been successfully filled I would like to re-dimension it (or something) to remove the rows that were not needed so that I can send it to a sorting function and not have the rows of non used entries. Thanks for any help Brandt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ReDim Array | Excel Programming | |||
ReDim Array | Excel Programming | |||
ReDim Object array as parameter of Variant array | Excel Programming | |||
Dim / Redim of an Array | Excel Programming | |||
ReDim an Array | Excel Programming |