Solved

# Building array based on cell value

Posted on 2015-02-07
151 Views
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
Question by:Stephen Byrom
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 34

Accepted Solution

Norie earned 500 total points
ID: 40596273
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

LVL 48

Expert Comment

ID: 40596429
Can you post a sample workbook?
0

LVL 1

Author Closing Comment

ID: 40596565
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦