(Solved) – Range to Array getting gummed up somehow in VBA

  • by

I am looking to copy a range into an array. The range is two columns and unknown rows.

This code works perfectly in a fresh workbook but not in my big complicated workbook.

What could be going wrong? in the complicated workbook, the below code shows the correct number of message boxes (and LastRow is right) but blanks for the array values.

Sub Button1_Click()
    Dim pN() As Variant
    Dim LastRow As Integer

    With Worksheets("merp")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        ReDim pN(1 To LastRow, 1 To 2)
        pN = .Range("A1:B" & LastRow).Value

        MsgBox LastRow

        For i = 1 To LastRow
            MsgBox CStr(pN(i, 1)) & " and " & CStr(pN(i, 2))

    End With
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *