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