Adding entire rows to array

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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
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?

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
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?

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
1P#BrandStyleFinishBig sizeLittle SizeSizeOffsetBBBP1BP2PriceUPCWeightIMGTitleDescQTYCenter
212345HooplerBlepBlack401040x10246.445x1201923740x10 Blep Hoopler Black270.6
312333GangisBloopBlack401040x10457.275x1201923740x10 Bloop Gangis Black170.6
412789NorpBlapBlack40940x9306.183x112.51863640x9 Blap Norp Black070.6
512348GangisBloopBlack398.539x8.5305.935x1202193739x8.5 Bloop Gangis Black170.6

<tbody>
</tbody>
CondensedSheets



this is description helper (os) ary2
ABCDEFGHIJKLMNO
12PCDMin OffserMax OffsetMAKEShortCodeRankTSW TABLEMin OffserMax OffsetMAKEShortCodeRank
135x1202335BloonduffBloonTROA15x1202345ZinksZinkB8SE1Hoopler
145x1202345BloonduffBloonHCIV15x1202335ZinksZinkB5SE1Hoopler
155x1202535BloonduffBloonSFOR25x1202535ZinksZinkB7SE2Hoopler
165x1203545BloonduffBloonMCOO25x1202535ZinksZinkBX7X4Hoopler
175x1083545RedwoodRedMMIA35x1083545ZinksZinkJXK1Hoopler
185x1082345RedwoodRedMCLUB45x1083545ZinksZinkJSTY2Hoopler
195x1083545RedwoodRedTPRIC55x1083545ZinksZinkJXJ3Hoopler

<tbody>
</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
1P#BrandStyleFinishBig sizeLittle SizeSizeOffsetBBBP1BP2PriceUPCWeightIMGTitleDescQTYCenter
212345HooplerBlepBlack401040x10246.445x1201923740x10 Blep Hoopler Black270.6
312333GangisBloopBlack401040x10457.275x1201923740x10 Bloop Gangis Black170.6
412789NorpBlapBlack40940x9306.183x112.51863640x9 Blap Norp Black070.6
512348GangisBloopBlack398.539x8.5305.935x1202193739x8.5 Bloop Gangis Black170.6
612345^B8SEHooplerBlepBlack401040x10246.445x1201923740x10 Blep Hoopler Black270.6
712345^B5SEHooplerBlepBlack401040x10246.445x1201923740x10 Blep Hoopler Black270.6
812333^HCIVGangisBloopBlack401040x10457.275x1201923740x10 Bloop Gangis Black170.6
912333^MCOOGangisBloopBlack401040x10457.275x1201923740x10 Bloop Gangis Black170.6
1012348^TROAGangisBloopBlack398.539x8.5305.935x1202193739x8.5 Bloop Gangis Black170.6
1112348^HCIVGangisBloopBlack398.539x8.5305.935x1202193739x8.5 Bloop Gangis Black170.6
1212348^SFORGangisBloopBlack398.539x8.5305.935x1202193739x8.5 Bloop Gangis Black170.6

<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>
</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
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

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

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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