Adding entire rows to array

DataBlake

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
I'm trying to write a row to the end of an array
the logic is essentially:

i = loop through UBound of array 1
x = loop through UBound of array 2
if statement is true then

add the entire row we are looping through from array 1 to the bottom of the array
make changes to the new line and add 4 new values the columns of the row

so what i have so far looks like

Code: 
Dim ary1 As Variant, ary2 As Variant
Dim ws As Worksheet, os As Worksheet
Dim i As Long, x As Long, j As Long
Dim lastRow As Long
Dim destRow As Long



Set ws = Sheets("CondensedSheets")
Set os = Sheets("Description Helper")

ary1 = ws.Range("A1").CurrentRegion.Value2
ary2 = os.Range("A13").CurrentRegion.Value2
lastRow = ws.Range("A" & Rows.count).End(xlUp).Row
destRow = lastRow + 1

For i = LBound(ary1) To UBound(ary1)
    For x = LBound(ary2) To UBound(ary2)
        If ary1(i, 2) = ary2(x, 15) Then
            If (ary1(i, 10) = ary2(x, 8) _
                Or ary1(i, 11) = ary2(x, 8)) _
                And ary1(i, 8) >= ary2(x, 2) _
                And ary1(i, 8) <= ary2(x, 3) _
                And j < 5 Then
                j = j + 1

 
        'increment row
        destRow = destRow + 1
        ReDim ary1(1 To destRow, 1 To (UBound(ary1, 2) + 4))
        
        'write the new row in the array
        [COLOR=#ff0000][B]ary1(desRow, ???) = ary1(???) [/B][/COLOR]

highlighted in red is where i'm stuck
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
This line
Code: 
ReDim ary1(1 To destRow, 1 To (UBound(ary1, 2) + 4))
will erase all the data in the array.

To keep the data intact you would have to use Redim Preserve, but that only allows you to change the 2nd dimension, not the first.
Do you need to add another row to the array, or can you just change the existing values?
 
Upvote 0
Both ary1 and ary2 are 2-D arrays. If you want to preserve these arrays when you ReDim them, you can only change the last dimension (2nd dimension in this case). Your explanation of what you want the code to do seems to indicate you want to add on rows to the original array, i.e. any ReDIm would be a ReDim Preserve, but your actual code doesn't include the Preserve so maybe I'm not understanding what you want.

In any case, memory is cheap, so why not make the original array sufficiently larger in row count than the actual current region to accommodate the maximum number of rows you will ever expect to require? Then, no redim is needed and any unfilled elements at the end of the array will be empty and essentially invisible for whatever purposes you have in mind for the final array.
 
Upvote 0
Fluff said:
This line
Code: 
ReDim ary1(1 To destRow, 1 To (UBound(ary1, 2) + 4))
will erase all the data in the array.

To keep the data intact you would have to use Redim Preserve, but that only allows you to change the 2nd dimension, not the first.
Do you need to add another row to the array, or can you just change the existing values?
Click to expand...

add a new row.
so if possible maybe a 3rd array that is array 1 + the altered lines?
basically its saying if the conditions match take that match and add it to the bottom of the array
then make changes to the new line
 
Upvote 0
In that case I'd suggest using JoeMo's approach
 
Upvote 0
JoeMo said:
In any case, memory is cheap, so why not make the original array sufficiently larger in row count than the actual current region to accommodate the maximum number of rows you will ever expect to require?
Click to expand...

i've found that the way i've created the array
Code: 
.Range("A1").CurrentRegion.Value2

is the fastest method of doing so. i work with an absurd amount of data so efficiency is really important to me
thats just how i've been doing it, but i do not know how many matches i should be accounting for? i work with an absurd amount of data,
i can try and clarify the goal here:

ws is a sheet (no idea how big it is because its dynamic and often hundreds of thousands lines of data) that i assign to ary1
os is a sheet of information that i can use to generate extra details about an item that i assign to ary2

for every line in ws i need to go through two tables on os
if ary1(i,2) = ary2(x,15) then
we need to match the values of ary1 that i specify in the if statement to the values of ary2 that i also specify in the if statement
if all of those "or"s and "and"s turn out to be true i need the row that ary1(i,2) is from to be duplicated
i then need to make changes to the row that was duplicated

i want to work in arrays because its so fast compared to looping through the data regularly
 
Upvote 0
As long as the number of rows in ary1 plus the number of rows in ary2 never exceeds 1048576, you could try
Code: 
Ary2 = os.Range("A13").CurrentRegion.Value2
With Ws.Range("A1").CurrentRegion
   Ary1 = .Resize(.Rows.Count + UBound(Ary2), .Columns.Count + 4).Value2
End With
 
Upvote 0
If the current region is the most efficient way to acquire the data for you, maybe something like this:
Code: 
Sub test()
Dim R As Range, N As Long, ary1
Set R = Range("A1").CurrentRegion
N = 10  'Change to suit
Set R = R.Resize(R.Rows.Count + N, R.Columns.Count)
ary1 = R.Value2
End Sub
Based on your experience, assign a value to N that you are reasonably confident will be ample. You can always build in a check to ensure that number of additonal rows is not exceeded during code execution.
 
Upvote 0
i have not tried out the approaches provided yet but i thought this would paint a clearer picture
this is condensedsheets (ws) ary1
ABCDEFGHIJKLMNOPQRS
P#BrandStyleFinishBig sizeLittle SizeSizeOffsetBBBP1BP2PriceUPCWeightIMGTitleDescQTYCenter
HooplerBlepBlack40x105x12040x10 Blep Hoopler Black
GangisBloopBlack40x105x12040x10 Bloop Gangis Black
NorpBlapBlack40x93x112.540x9 Blap Norp Black
GangisBloopBlack39x8.55x12039x8.5 Bloop Gangis Black

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]12345[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]24[/TD]
[TD="align: right"]6.44[/TD]

[TD="align: right"][/TD]
[TD="align: right"]192[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]70.6[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]12333[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]7.27[/TD]

[TD="align: right"][/TD]
[TD="align: right"]192[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]70.6[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]12789[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]6.18[/TD]

[TD="align: right"][/TD]
[TD="align: right"]186[/TD]
[TD="align: right"][/TD]
[TD="align: right"]36[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]70.6[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12348[/TD]

[TD="align: right"]39[/TD]
[TD="align: right"]8.5[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]5.93[/TD]

[TD="align: right"][/TD]
[TD="align: right"]219[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]70.6[/TD]

</tbody>
CondensedSheets​



this is description helper (os) ary2
ABCDEFGHIJKLMNO
PCDMin OffserMax OffsetMAKEShortCodeRankTSW TABLEMin OffserMax OffsetMAKEShortCodeRank
5x120BloonduffBloonTROA5x120ZinksZinkB8SEHoopler
5x120BloonduffBloonHCIV5x120ZinksZinkB5SEHoopler
5x120BloonduffBloonSFOR5x120ZinksZinkB7SEHoopler
5x120BloonduffBloonMCOO5x120ZinksZinkBX7XHoopler
5x108RedwoodRedMMIA5x108ZinksZinkJXKHoopler
5x108RedwoodRedMCLUB5x108ZinksZinkJSTYHoopler
5x108RedwoodRedTPRIC5x108ZinksZinkJXJHoopler

<tbody>
[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]35[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]35[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]35[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]35[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]35[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]35[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]35[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]35[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]35[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]35[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]35[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"]3[/TD]

</tbody>
Decsription Helper
​



so from H-O is the second table that we need to access first

the end result of the "new" array should be this
ABCDEFGHIJKLMNOPQRSTUVW
P#BrandStyleFinishBig sizeLittle SizeSizeOffsetBBBP1BP2PriceUPCWeightIMGTitleDescQTYCenter
HooplerBlepBlack40x105x12040x10 Blep Hoopler Black
GangisBloopBlack40x105x12040x10 Bloop Gangis Black
NorpBlapBlack40x93x112.540x9 Blap Norp Black
GangisBloopBlack39x8.55x12039x8.5 Bloop Gangis Black
12345^B8SEHooplerBlepBlack40x105x12040x10 Blep Hoopler Black
12345^B5SEHooplerBlepBlack40x105x12040x10 Blep Hoopler Black
12333^HCIVGangisBloopBlack40x105x12040x10 Bloop Gangis Black
12333^MCOOGangisBloopBlack40x105x12040x10 Bloop Gangis Black
12348^TROAGangisBloopBlack39x8.55x12039x8.5 Bloop Gangis Black
12348^HCIVGangisBloopBlack39x8.55x12039x8.5 Bloop Gangis Black
12348^SFORGangisBloopBlack39x8.55x12039x8.5 Bloop Gangis Black

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]12345[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]24[/TD]
[TD="align: right"]6.44[/TD]

[TD="align: right"][/TD]
[TD="align: right"]192[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]70.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]12333[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]7.27[/TD]

[TD="align: right"][/TD]
[TD="align: right"]192[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]70.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]12789[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]6.18[/TD]

[TD="align: right"][/TD]
[TD="align: right"]186[/TD]
[TD="align: right"][/TD]
[TD="align: right"]36[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]70.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12348[/TD]

[TD="align: right"]39[/TD]
[TD="align: right"]8.5[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]5.93[/TD]

[TD="align: right"][/TD]
[TD="align: right"]219[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]70.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]24[/TD]
[TD="align: right"]6.44[/TD]

[TD="align: right"][/TD]
[TD="align: right"]192[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]70.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]24[/TD]
[TD="align: right"]6.44[/TD]

[TD="align: right"][/TD]
[TD="align: right"]192[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]70.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]7.27[/TD]

[TD="align: right"][/TD]
[TD="align: right"]192[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]70.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]7.27[/TD]

[TD="align: right"][/TD]
[TD="align: right"]192[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]70.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]39[/TD]
[TD="align: right"]8.5[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]5.93[/TD]

[TD="align: right"][/TD]
[TD="align: right"]219[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]70.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]39[/TD]
[TD="align: right"]8.5[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]5.93[/TD]

[TD="align: right"][/TD]
[TD="align: right"]219[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]70.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]39[/TD]
[TD="align: right"]8.5[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]5.93[/TD]

[TD="align: right"][/TD]
[TD="align: right"]219[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]70.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
CondensedSheets​




because for each row in CondensedSheets we use that big if statement to say
if the brand is in DescriptionHelper Column O then
if the BP1 or BP2 = Column A
and the offset is <= C
and the offset is >= B
and j < 5
then copy the row and make changes (including the + 4 columns on the end which i will get to after the column A change)
elseif the brand is NOT in DescriptionHelper Column O then
BP1 or BP2 = Column H
and the offset is <= J
and the offset is >= I
and j < 5
then copy the row and make changes (including the + 4 columns on the end which i will get to after the column A change)

if thats makes sense this is the idea in full
 
Upvote 0
JoeMo said:
Code: 
Sub test()
Dim R As Range, N As Long, ary1
Set R = Range("A1").CurrentRegion
N = 10  'Change to suit
Set R = R.Resize(R.Rows.Count + N, R.Columns.Count)
ary1 = R.Value2
End Sub
Click to expand...

okay so i think this will work as far as setting the size of the array, but how would i go about putting the values into it?
loop through until isempty?

Code: 
Set R = ws.Range("A1").CurrentRegion
ary2 = os.Range("A13").CurrentRegion.Value2
lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
Set R = R.Resize(lastRow + lastRow + lastRow + lastRow, R.Columns.Count + 4)
ary1 = R.Value2

or do you think i should just go with a 3rd array to write the matched results to?
that could possibly work?

Code: 
ary1 = ws.Range("A1").CurrentRegion.Value2
ary2 = os.Range("A13").CurrentRegion.Value2
lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
ReDim ary3(1 To (lastRow * 4), 1 To (UBound(ary1, 2) + 4))

and write the matched results of the if statement to this ary3
and then write ary3 lastrow + 1 of the sheet

my brain hurts
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Building Custom Function with Array Loop
Replies
3
Views
112
FormR
FormR
2
  • Question Question
Compare columns with discontinuous data.
Replies
3
Views
135
24August
2
P
  • Solved
Select Case in VBA
Replies
3
Views
752
rollis13
rollis13
U
  • Question Question
Excel VBA, Loop, Copy, Loop, Paste, Autofilter, return value, Loop.
Replies
0
Views
136
user29383902
U
A
  • Question Question
Code to Add Data From UserForm In Row Above or Below Based on Criteria
Replies
1
Views
488
amoverton2
A

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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