• Status: Solved
• Priority: Medium
• Security: Public
• Views: 161

# Building array based on cell value

Hi,
I'm new to arrays, and slowly getting better with vba.
I know how to use 'Do' or 'For Next' loops, but I have a sheet with about 15,000 rows of data and need to populate another sheet with data from the large range. So for example, gather all the rows relating to a certain month number. I have been reading that building a temporary array and passing the result to the sheet would be much quicker than using a loop. I can find examples of getting arrays from contiguous data, but I only want to add rows from the data where, for example, the value of column 5 is greater than 0. If anyone can point me in the right direction it would be much appreciated.
0
Stephen Byrom
1 Solution

VBA ExpertCommented:
You could put all the data in an array, then create another array from that array using your criteria and then transfer the new array to the other worksheet.
``````Dim arrData As Variant
Dim arrFiltered() As Variant
Dim I As Long
Dim J As Long
Dim Cnt As Long

arrData = Sheets("Sheet1").UsedRange.Value

For I = LBound(arrData) To UBound(arrData)
If arrData(I, 5) > 0 Then
Cnt = Cnt + 1
ReDim Preserve arrFiltered(1 To UBound(arrData, 2), 1 To Cnt)
For J = LBound(arrData, 2) To UBound(arrData, 2)
arrFiltered(J, Cnt) = arrData(I, J)
Next J
End If
Next I

If Cnt > 0 Then
Sheets("Sheet2").Range("A1").Resize(UBound(arrFiltered, 2), UBound(arrFiltered, 1)).Value = Application.Transpose(arrFiltered)
End If
``````
0

Older than dirtCommented:
Can you post a sample workbook?
0

Warehouse/ShippingAuthor Commented:
OMG !!
So much quicker than looping through the data.
I will study your code more deeply later and try to build other arrays myself, but for now it works perfectly on the data I have been given.
Thank you so much for your time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.