1. ## Adding entire rows to array

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
ary1(desRow, ???) = ary1(???) ```
highlighted in red is where i'm stuck

2. ## Re: Adding entire rows to array

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?

3. ## Re: Adding entire rows to array

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.

4. ## Re: Adding entire rows to array

Originally Posted by Fluff
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?
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

5. ## Re: Adding entire rows to array

In that case I'd suggest using JoeMo's approach

6. ## Re: Adding entire rows to array

Originally Posted by JoeMo
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

7. ## Re: Adding entire rows to array

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

8. ## Re: Adding entire rows to array

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.

9. ## Re: Adding entire rows to array

i have not tried out the approaches provided yet but i thought this would paint a clearer picture
this is condensedsheets (ws) ary1
A B C D E F G H I J K L M N O P Q R S
1 P# Brand Style Finish Big size Little Size Size Offset BB BP1 BP2 Price UPC Weight IMG Title Desc QTY Center
2 12345 Hoopler Blep Black 40 10 40x10 24 6.44 5x120 192 37 40x10 Blep Hoopler Black 2 70.6
3 12333 Gangis Bloop Black 40 10 40x10 45 7.27 5x120 192 37 40x10 Bloop Gangis Black 1 70.6
4 12789 Norp Blap Black 40 9 40x9 30 6.18 3x112.5 186 36 40x9 Blap Norp Black 0 70.6
5 12348 Gangis Bloop Black 39 8.5 39x8.5 30 5.93 5x120 219 37 39x8.5 Bloop Gangis Black 1 70.6
CondensedSheets

this is description helper (os) ary2
A B C D E F G H I J K L M N O
12 PCD Min Offser Max Offset MAKE Short Code Rank TSW TABLE Min Offser Max Offset MAKE Short Code Rank
13 5x120 23 35 Bloonduff Bloon TROA 1 5x120 23 45 Zinks Zink B8SE 1 Hoopler
14 5x120 23 45 Bloonduff Bloon HCIV 1 5x120 23 35 Zinks Zink B5SE 1 Hoopler
15 5x120 25 35 Bloonduff Bloon SFOR 2 5x120 25 35 Zinks Zink B7SE 2 Hoopler
16 5x120 35 45 Bloonduff Bloon MCOO 2 5x120 25 35 Zinks Zink BX7X 4 Hoopler
17 5x108 35 45 Redwood Red MMIA 3 5x108 35 45 Zinks Zink JXK 1 Hoopler
18 5x108 23 45 Redwood Red MCLUB 4 5x108 35 45 Zinks Zink JSTY 2 Hoopler
19 5x108 35 45 Redwood Red TPRIC 5 5x108 35 45 Zinks Zink JXJ 3 Hoopler
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
A B C D E F G H I J K L M N O P Q R S T U V W
1 P# Brand Style Finish Big size Little Size Size Offset BB BP1 BP2 Price UPC Weight IMG Title Desc QTY Center
2 12345 Hoopler Blep Black 40 10 40x10 24 6.44 5x120 192 37 40x10 Blep Hoopler Black 2 70.6
3 12333 Gangis Bloop Black 40 10 40x10 45 7.27 5x120 192 37 40x10 Bloop Gangis Black 1 70.6
4 12789 Norp Blap Black 40 9 40x9 30 6.18 3x112.5 186 36 40x9 Blap Norp Black 0 70.6
5 12348 Gangis Bloop Black 39 8.5 39x8.5 30 5.93 5x120 219 37 39x8.5 Bloop Gangis Black 1 70.6
6 12345^B8SE Hoopler Blep Black 40 10 40x10 24 6.44 5x120 192 37 40x10 Blep Hoopler Black 2 70.6
7 12345^B5SE Hoopler Blep Black 40 10 40x10 24 6.44 5x120 192 37 40x10 Blep Hoopler Black 2 70.6
8 12333^HCIV Gangis Bloop Black 40 10 40x10 45 7.27 5x120 192 37 40x10 Bloop Gangis Black 1 70.6
9 12333^MCOO Gangis Bloop Black 40 10 40x10 45 7.27 5x120 192 37 40x10 Bloop Gangis Black 1 70.6
10 12348^TROA Gangis Bloop Black 39 8.5 39x8.5 30 5.93 5x120 219 37 39x8.5 Bloop Gangis Black 1 70.6
11 12348^HCIV Gangis Bloop Black 39 8.5 39x8.5 30 5.93 5x120 219 37 39x8.5 Bloop Gangis Black 1 70.6
12 12348^SFOR Gangis Bloop Black 39 8.5 39x8.5 30 5.93 5x120 219 37 39x8.5 Bloop Gangis Black 1 70.6
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

10. ## Re: Adding entire rows to array

Originally Posted by JoeMo
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