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

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top