Results 1 to 8 of 8

Thread: Redim array based on IsEmpty
Thanks Thanks: 0 Likes Likes: 0

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

    Default Redim array based on IsEmpty

    so in short this is just using arrays to delete blank rows, but it would help me understand array ranges more if i could get this.

    I am trying to have an array speak to another array
    basically i have a sheet with lets say 1,000 rows of data and 200 of these rows has an empty value in column B
    I tell array 1 (ary1) that it is A2:F & lastRow
    Then i want to loop through ary1 to build array 2 (ary2)
    Where if Column B is blank in Array 1 DO NOT write to array 2
    but if it contains something then expand array 2 by 1 row and write the values from ary1 to ary2


    Code:
    Option Explicit
    Sub exportQTY()
    
    Sub exportQTY()
    
    'establish sheet arrays and dictionary object
    Dim ary1 As Variant
    Dim ary2 As Variant
    Dim i As Long
    Dim lastRow As Long
    
    lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    ary1 = ActiveSheet.Range("A2:F" & lastRow).Value2
    
    For i = LBound(ary1) To UBound(ary1)
    If IsEmpty(ary1(i, 2)) Then
    ElseIf Not IsEmpty(ary1(i, 2)) Then
    ReDim Preserve ary2(1 To UBound(ary2) + 1)
    ary2(UBound(ary2)) = ary1(i).Value
    End If
    Next i
    
    End Sub
    but i get a type mismatch on the ReDim line
    ary2 should be the 800 rows of data up to F column that don't have blanks in column B
    -------------------------------------------------------------------------------
    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,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Redim array based on IsEmpty

    How about
    Code:
    Sub exportQTY()
    
    'establish sheet arrays and dictionary object
    Dim ary1 As Variant
    Dim ary2 As Variant
    Dim i As Long, j As Long, k As Long
    Dim lastRow As Long
    
    lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    ary1 = ActiveSheet.Range("A2:F" & lastRow).Value2
    ReDim ary2(1 To UBound(ary1), 1 To UBound(ary1, 2))
    For i = LBound(ary1) To UBound(ary1)
       If Not IsEmpty(ary1(i, 2)) Then
          j = j + 1
          For k = 1 To UBound(ary1, 2)
             ary2(j, k) = ary1(i, k)
          Next k
       End If
    Next i
    End Sub
    You have to loop trough the columns to populate ary2
    - 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
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Redim array based on IsEmpty

    Code:
    ReDim ary2(1 To UBound(ary1), 1 To UBound(ary1, 2))
    so here its saying to establish ary2 row from 1 to ary1 row
    and ary2 column from 1 to ary1 column?


    Code:
    For i = LBound(ary1) To UBound(ary1)
    then we loop through ary1 rows

    Code:
    If Not IsEmpty(ary1(i, 2)) Then
          j = j + 1
          For k = 1 To UBound(ary1, 2)
    and then if column B in ary1 is empty we loop through the columns and use j as a place holder for the current row of ary2 we are on?


    if thats correct: does that mean there are a bunch of unused rows in the end of ary2 since we established ary2 to UBound(ary1)?
    if yes, will these blank rows have to be accounted for when writing the array to a sheet?
    Last edited by BlakeSkate; Aug 29th, 2019 at 02:27 PM.

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

    Default Re: Redim array based on IsEmpty

    so here its saying to establish ary2 row from 1 to ary1 row
    and ary2 column from 1 to ary1 column?
    Basically yes, its simply making ary2 the same size as ary1

    then we loop through ary1 rows
    That's right

    and then if column B in ary1 is empty we loop through the columns and use j as a place holder for the current row of ary2 we are on?
    Yup, you got it.

    does that mean there are a bunch of unused rows in the end of ary2 since we established ary2 to UBound(ary1)?
    if yes, will these blank rows have to be accounted for when writing the array to a sheet?
    Yes & it can be done like
    Code:
    Sheets("Sheet2").Range("A1").Resize(j, UBound(ary2, 2)).Value = ary2
    Although you could still write the entire ary2 to a sheet, as the empty rows won't matter.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Redim array based on IsEmpty

    Quote Originally Posted by Fluff View Post
    Code:
    Sheets("Sheet2").Range("A1").Resize(j, UBound(ary2, 2)).Value = ary2
    as the empty rows won't matter.
    ahhhh
    so this will write the array to resize up until where the last row of data was?
    so this as opposed to

    Code:
    Sheets("Sheet2").Range("A2").Resize(UBound(ary2), 6)
    i guess this is the most confusing part for me
    what if i wanted to write this array between datasets
    so lets say on sheet 3 i have a header, and i insert 800 rows to make room for writing ary2 ABOVE data that i already have
    the resizing with j will write only the 800 rows and not the 200 blank rows as well?
    -------------------------------------------------------------------------------
    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

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

    Default Re: Redim array based on IsEmpty

    Yup that's right
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Redim array based on IsEmpty

    Quote Originally Posted by Fluff View Post
    Yup that's right
    Brilliant.
    Thanks for the clarification
    -------------------------------------------------------------------------------
    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

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

    Default Re: Redim array based on IsEmpty

    My pleasure
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •