Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Adding entire rows to array
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    460
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default 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?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,453
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default 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.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  4. #4
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    460
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding entire rows to array

    Quote Originally Posted by Fluff View Post
    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
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Adding entire rows to array

    In that case I'd suggest using JoeMo's approach
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    460
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding entire rows to array

    Quote Originally Posted by JoeMo View Post
    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
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default 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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,453
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default 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.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  9. #9
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    460
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  10. #10
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    460
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding entire rows to array

    Quote Originally Posted by JoeMo View Post
    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 by BlakeSkate; Sep 13th, 2019 at 04:04 PM.
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •