Add ranges to array

H

henrik2h

Board Regular
Joined
Aug 25, 2008
Messages
155
Office Version
  1. 2021
Platform
  1. Windows
Hi, been a while since last time...
Right now I have a large amount of calculations that produce a range (results) of outcome on the spreadsheet (calc). A single input change all the outcome. I have a macro that loop through all the input (1...1000), copy results and then transpose and paste that range onto another sheet (pivotdata). It then takes the next input and place the new results below the existing data on pivotdata.

Question
The macro is fairly slow, would I benefit from storing all the results-ranges one by one "below" each other into an array instead and then paste the entire array onto pivotdata? How would I go about to do that?

Best regards
Henrik
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
I think it is possible to improve performance using arrays, but your question is a bit vague.
An example would be useful - a small sample of data along with the expected results.

M.
 
Upvote 0
Something like this (macro loop through input one by one)

Code: 
[TABLE="width: 522"]
<tbody>[TR]
[TD="colspan: 2"]Sheet "Calc"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Results[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Input[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Sheet "Pivotdata"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Assuming data like this

Sheet Calc


A​
B​
C​
D​
E​
F​
1​
Results​
2​
Input​
1​
3​
4​

<tbody>
</tbody>


Sheet PivotData

A​
B​
1​
Header1​
Header2​
2​
1​
1​
3​
1​
1​
4​
1​
1​
5​
1​
1​
6​
1​
1​
7​
2​
2​
8​
2​
2​
9​
2​
2​
10​
2​
2​
11​
2​
2​
12​
3​
3​
13​
3​
3​
14​
3​
3​
15​
3​
3​
16​
3​
3​
17​

<tbody>
</tbody>


Maybe this macro
Code: 
Sub aTest()
    Dim arr() As Variant, rData As Range, vData As Variant
    Dim i As Long, j As Long, LastRow As Long
    Dim NumElem As Long, vCrit As Variant
    
    With Sheets("Calc")
        vCrit = .Range("B2").Value
        LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
    End With
    
    With Sheets("PivotData")
        Set rData = .Range("A2:B" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        NumElem = Application.CountIf(rData.Columns(1), vCrit)
    End With
    ReDim arr(1 To 2, 1 To NumElem)
    
    vData = rData
    For i = LBound(vData, 1) To UBound(vData, 1)
        If vData(i, 1) = vCrit Then
            j = j + 1
            arr(1, j) = vData(i, 1)
            arr(2, j) = vData(i, 2)
        End If
    Next i
    
    Sheets("Calc").Range("E" & LastRow + 1).Resize(2, NumElem) = arr
End Sub

M.
 
Upvote 0
Thank you, I will look into it in more detail and see if it improves performance.

Have a nice weekend!
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
VBA Join arrays together
Replies
7
Views
255
henrik2h
H
T
  • Question
Excel program help for a prediction league
Replies
1
Views
465
Drrellik
Drrellik
P
  • Solved
single-formula combining of multiple dynamic arrays (VSTACK?)
Replies
4
Views
879
Plod518
P
L
  • Question
User Defined Formula to Generate Polynomial Regression Equation
Replies
1
Views
452
Dave Patton
D
D
  • Question
Trying to create a results sheet using two data ranges
Replies
0
Views
183
Doot
D

Forum statistics

Threads
1,215,373
Messages
6,124,549
Members
449,170
Latest member
Gkiller

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top