CODEWITHSUNDEEP
excelvbaexcel-2007
Sabha
Sabha

Reputation: 609

Create multiple sheets to dump array data

Please refer to the code below which needs to be tweaked where I am facing difficulties.

There is an array m3a which has a large amount of data which is dumped in a new worksheet when the code is completed. If the data exceeds the number of max rows in excel (1048576), it adds the top 1048575 data in a new array m4a and dumps it. I wish to know that if the data exceeds, how multiple sheets can be created (two sheets, three sheets... etc depending on number of rows in the array. Please help me tweak this piece of code

iLines = 3
startCalc = True
If startCalc Then
  Worksheets.Add After:=Worksheets(Worksheets.Count)
   Set sh = ActiveSheet
   If UBound(m3a, 1) <= Rows.Count Then
    sh.Range("A1").Resize(cnt, iLines + 1).Value = m3a
   Else
     ReDim m4a(1 To 1048575, 1 To iLines + 1)

       For i = 1 To 1048575
         For j = 1 To iLines + 1

            m4a(i, j) = m3a(i, j)
         Next j
       Next i
       sh.Range("A1").Resize(1048575, iLines + 1).Value = m4a
   End If
End If

Upvotes: 0

Views: 153

Answers (1)

SJR
SJR

Reputation: 23081

Maybe this example will help. I'm using a smaller array 105 elements and moving 10 rows at a time so this gives 10 sheets with 10 rows and 1 sheet with 5 rows. You don't need to populate your array as you already have it. A million rows would paralyse my work system. Good luck ...

Edit: updated for 2D array.

Sub x()

Dim v(1 To 105, 1 To 2), i As Long, j As Long, ws As Worksheet, n As Long

n = 10 'number of rows transferred to each sheet

For i = LBound(v, 1) To UBound(v, 1) 'populating array just for this example
    v(i, 1) = i
    v(i, 2) = i * i
Next i

Do
    If UBound(v, 1) - j <= n Then n = UBound(v, 1) - j
    Set ws = Worksheets.Add
    ws.Range("A1").Resize(n, 2).Value = Application.Index(v, Evaluate("row(" & j + 1 & ":" & n + j & ")"), Array(1, 2))
    j = j + n
    If j >= UBound(v, 1) Then Exit Sub
Loop

End Sub

Upvotes: 1

Related Questions