Redim array based on IsEmpty

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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