VBA - remove blanks from an 2 dimensional array

Question

Hello,

I have a number of ranges on a worksheet that contain blanks. I'm trying to remove the blanks from each range and write the new, blank free ranges to new locations on the worksheet. I'm struggling to create the new blank free array because I'm getting a subscript out of range error on the following code:

Sub SetRangeWithoutBlanks()

Dim rng As Range

Set rng = Range("K1:K10")

Dim arMyArray() As Variant

Dim NewArr() As Variant

arMyArray = Range("A1:a10").Value

For i = LBound(arMyArray) To UBound(arMyArray)

    ReDim NewArr(LBound(arMyArray) To UBound(arMyArray))

    If arMyArray(i, 1) = "" Then

        j = j + 1

        'errors here

        NewArr(j, 1) = arMyArray(i, 1)

    End If

Next i

'Debug.Print arMyArray(i, 1)

ReDim Preserve NewArr(LBound(arMyArray) To j)

End Sub

Can anyone tell me where I'm going wrong.

Thanks in advance,

Mark

Answer 1

The way you are handling the array is wrong. I would recommend that you invest 1 hour in reading below to have an expert level knowledge of Arrays in VBA. After this, you would be able to handle array properly.

If after this reading, you stuck in some problem, post back for the help. I will provide that help.

The Complete Guide to Using Arrays in Excel VBA - https://excelmacromastery.com/excel-vba-array/

VBA Arrays And Worksheet Ranges -http://www.cpearson.com/excel/ArraysAndRanges.aspx