How can I redim my array for the next file I open up?

Viewed 98

I have an script built to go into a file in a dir and form an array based off of the data.

How would I print this:

Dim k As Long, x As Long, j As Long ' counters
Dim varArray() As Variant
ReDim varArray(1 To 13, 1 To 1)

ReDim varArray(1 To UBound(varArray, 1), 1 To 1)

        For j = 1 To .UsedRange.Rows.Count + 1
            If .Cells(j, 1) <> "" Then
                x = x + 1
                ReDim Preserve varArray(1 To UBound(varArray, 1), 1 To x)
                    For k = 1 To UBound(varArray, 1)
                        varArray(k, x) = .Cells(j, k)
                    Next
            End If
        Next

This builds the array, but I now need it printed to the sheet, so I wrote this:

    With Workbooks("master.xlsm").Worksheets("Sheet2")


            For j = 2 To UBound(varArray, 2)
                For k = 1 To UBound(varArray, 1)
                    ActiveSheet.Cells(j, k) = varArray(k, j)
                Next
            Next

    End With

This works, but now I need to clear out the array after it has printed with a Redim, but this:

ReDim varArray(1 To UBound(varArray, 1), 1 To 1)

doesn't clear it after I place it after the last end with

Here's the whole script for the sake of the bigger picture:

Option Explicit

Sub Stuff()

Dim k As Long, x As Long, j As Long ' counters
Dim varArray() As Variant
ReDim varArray(1 To 13, 1 To 1)

Dim MyFile As String


MyFile = Dir("M:\Merge Files\")

If Len(MyFile) <> 0 Then




    With ThisWorkbook.Worksheets(1)

        ReDim varArray(1 To UBound(varArray, 1), 1 To 1)

            For j = 1 To .UsedRange.Rows.Count + 1
                If .Cells(j, 1) <> "" Then
                    x = x + 1
                    ReDim Preserve varArray(1 To UBound(varArray, 1), 1 To x)
                        For k = 1 To UBound(varArray, 1)
                            varArray(k, x) = .Cells(j, k)
                        Next
                End If
            Next
    End With

        With Workbooks("master.xlsm").Worksheets("Sheet2")


                For j = 2 To UBound(varArray, 2)
                    For k = 1 To UBound(varArray, 1)
                        ActiveSheet.Cells(j, k) = varArray(k, j)
                    Next
                Next

        End With

ReDim varArray(1 To UBound(varArray, 1), 1 To 1)

End If


End Sub
0 Answers
Related