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

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

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

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