Remove Array Blank Rows

[JStellato]

Hello, I'm working with an array and am new to VBA, I have this worksheet:

Excel Workbook
	A	B	C	D	E	F	G	H
1	John	31	200	1		John	31	200
2	Ramone	24	200	2		Ramone	24	200
3	Ariel	2	50	3
4	Baby	0	10	4
5	John	31	200	1		John	31	200
6	Ramone	24	200	2		Ramone	24	200
7	John	31	200	1		John	31	200
8	Ramone	24	200	2		Ramone	24	200
Sheet1

The code I use to take the data on the left and output it to the right is this:


For lngRow = 1 To UBound(MyArray, 1)
If MyArray(lngRow, 3) * 10 <> 2000 Then
For lngCol = 1 To UBound(MyArray, 2)
MyArray(lngRow, lngCol) = vbNullString
Next
End If
Next

.Range("E1").Resize(UBound(MyArray, 1), UBound(MyArray, 2)) = MyArray


The problem is, I don't want the blank rows in the array, I would like the code to skip them. Basically this code is all from forum members, so I'm having a hard time understanding exactly how it's working. I know I want a "if isnull" or something similar but am unsure of how to implement it.

Thanks if you can help!

 

[brettdj]

hth

Dave

Sub Redo2()
    Dim MyArray()
    Dim lngRow As Long
    Dim lngCol As Long
    
    MyArray = Range([a1], [c8])

    For lngRow = 1 To UBound(MyArray, 1)
        If MyArray(lngRow, 3) * 10 <> 2000 Then
            For lngCol = 1 To UBound(MyArray, 2)
                MyArray(lngRow, lngCol) = vbNullString
            Next
        End If
    Next
    Columns("E:G").ClearContents
    Range("E1").Resize(UBound(MyArray, 1), UBound(MyArray, 2)) = MyArray
    On Error Resume Next
    Columns("E:G").SpecialCells(xlBlanks).Delete xlUp
End Sub