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