Redim array based on IsEmpty

BlakeSkate

Active Member
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
 

Fluff

MrExcel MVP, Moderator
Office Version
365
Operating System
Windows
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
 

BlakeSkate

Active Member
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:

Fluff

MrExcel MVP, Moderator
Office Version
365
Operating System
Windows
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.
 

BlakeSkate

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

Some videos you may like

This Week's Hot Topics

Top