Create multiple sheets to dump array data

Question

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

Why not have a loop which extracts a million rows at a time and terminates when fewer than 1m left? That said, a workbook with sheets of a million rows of data doesn't sound like much fun. — SJR
– SJR, Commented Feb 26, 2019 at 11:43
well.. i cant make any change in the code as i need all the data even if it crosses a million so adding another sheet or another multiple sheets if the data is more than 2 millions is the only option left out. I understand it doesn't make sense to have it in excel but later that data is transferred to another database. Appreciate if you could give me an idea of how to add multiple sheets where 1M records are saved in each sheet. If there are 4M records, 4 sheets needs to be added. Thanks for looking into it. — Sabha
– Sabha, Commented Feb 26, 2019 at 11:47
Does line UBound(m3a, 1) <= Rows.Count work as expected? I think it should be UBound(m3a) <= Rows.Count . Also, is the array m3a unidimensional or bidimensional? — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Feb 26, 2019 at 12:06
Arrays are bidimensiional and it m3a and m4a both works properly. — Sabha
– Sabha, Commented Feb 26, 2019 at 12:45

SJR · Accepted Answer · 2019-02-26 12:57:12Z

1

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

edited Feb 26, 2019 at 12:57

answered Feb 26, 2019 at 12:08

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sabha Over a year ago

By looking at the your example, I can say that it is for 1D array and my array is 2D. I may have to work a bit to match the logic with my 2D array example. Will try and get back if I face any issues. Thanks for your reply.

Sabha Over a year ago

I am trying to use your logic but unable to get it right and getting stuck at application.index. In your example, the values are in array v and in my example, the values are in array m3a so i dont need the For i loop as the array is full of data. Lets say there were 105 rows in m3a then what changes should I make to get multiple sheets? Sorry for the trouble and thanks for looking into it. btw m3a has 4 elements in each row that it why value of iLines is 3

SJR Over a year ago

Yes the clever bit is Evaluate("row(" & j + 1 & ":" & n + j & ")"), Array(1, 2) which takes a slice of the array, from the j+1 st element to the n+jthe element. In the first iteration that is 1 to 10, then it's 11 to 20 etc. The array bit refers to the 2 columns so if your array has has more then that will need changing. Otherwise, just use your array name. If it doesn't work, perhaps post an example of your array, just a few rows?

Collectives™ on Stack Overflow

Create multiple sheets to dump array data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related