Criteria based Row Array

[chirayu]

Hi Guys,

I'm sure I've read this somewhere. I think the macro uses a For Loop to create an Array of Rows that meet Criteria and then copies them all in 1 go to desired sheet. But can't seem to find the link for the life of me.

Example
Column A = Fruit Name e.g. Apple, Orange, Banana, Watermelon
Column B to D = Yearly Average Price e.g. 2015,2016,2017 prices
File has a sheet name for each of the fruits so using above example - there should be four sheets already created in the file with each sheet being named after the fruit

Based on above example then firstly the macro would either count number of sheets (minus main data sheet that has all fruits) or count unique fruit names from the data sheet. So that it know how many times to run the loop over the range

Secondly, during the loop the macro will loop through the range for each of the unique fruit names/ sheet names and build an array of rows that have the specific fruit name.

Thirdly it will use this array to copy specific row number e.g. from column A:D to the respective sheet that has the same fruit name.

Essentially I'm trying to figure out a way to work on a large dataset & distribute it to respective sheets without going line by line using Do Until/Do While

 

[Marc L]

Hi !

An advanced filter can create a list of unique fruits from data worksheet.
(Can also be done via a VBA Collection or an external Dictionary.)

Then an easy loop on this list can copy data of each fruit to its sheet
again via an advanced filter like in the thread
How to create separate Sheets workbook with name …
​

 

[Chihiro]

Normally, what I'd do to fill array is as following.

1. Load entire used range to variant array. Also have 2nd variant declared for later use.
2. Either use Application.CountIF or loop on the array to count records that meet the criteria. (You can also use dictionary here)
3. Redim 2nd array using dimension determined from step 2.
4. Loop through 1st array and load 2nd array using condition.
5. Load 2nd array back on to sheet.
Also I often use dictionary in conjunction with array to transform data.

You can see few example where I did that in this forum.
https://chandoo.org/forum/threads/e...nt-approach-to-speed-it-up.35561/#post-212854
https://chandoo.org/forum/threads/e...lues-between-two-dates-vba.35457/#post-212116
https://chandoo.org/forum/threads/matrix-to-list-database.35258/#post-210871

Though I'd recommend using PowerQuery, Advanced Filter or other methods unless scope is well defined and there's no worry of succession planning.

 

[shrivallabha]

Hi Guys,

I'm sure I've read this somewhere. I think the macro uses a For Loop to create an Array of Rows that meet Criteria and then copies them all in 1 go to desired sheet. But can't seem to find the link for the life of me.
:
:
Essentially I'm trying to figure out a way to work on a large dataset & distribute it to respective sheets without going line by line using Do Until/Do While

Do not be under misconception that "For Loop" will not iterate through rows like "Do Loop". They are just different loop constructs catering to different situations and by themselves don't have anything special like building an array automatically.

It still has to be an explicit command to add elements to array inside the loop. I am attaching example to demonstrate the same.

Public Sub ForAndDoLoop()
Dim oddElemArray(), oddElemArray2()
Dim lngCnt As Long, i As Long
ReDim oddElemArray(Range("A" & Rows.Count).End(xlUp).Row - 1)
lngCnt = LBound(oddElemArray)
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
    If Range("A" & i).Value Mod 2 = 1 Then
        oddElemArray(lngCnt) = Range("A" & i).Value
        lngCnt = lngCnt + 1
    End If
Next i
ReDim Preserve oddElemArray(lngCnt)
MsgBox "For Loop Result:" & vbCrLf & Join(oddElemArray, vbCrLf)

ReDim oddElemArray2(lngCnt)
lngCnt = LBound(oddElemArray2)
i = 1
Do While Range("A" & i).Value <> ""
    If Range("A" & i).Value Mod 2 = 1 Then
        oddElemArray2(lngCnt) = Range("A" & i).Value
        lngCnt = lngCnt + 1
    End If
    i = i + 1
Loop
MsgBox "Do  Loop Result:" & vbCrLf & Join(oddElemArray2, vbCrLf)
End Sub

 

[chirayu]

Thanks guys. Sorry couldn't respond sooner. Just been hectic at work. Will research the above & get back to you if needed.