Max number of rows for a sheet

[Danny54]

I have the following vba code that runs successfully if the rows created are < 1047666. The error is thrown on the following line at the end of the script where it tries to write the output.

[Sheet3!A1:B1].Resize(N).Value2 = S

Any suggestion on creating multiple pages should this value exceed the 1047666 row count or create a text output file that perhaps can have a greater line count.

Thanks



Sub Demo2()
Dim W, S$(), N&, K&, V, L$(), R$(), A%, T$(3), B%, C%, D%
W = [Sheet2!A1].CurrentRegion.Value2
ReDim S(1 To Rows.Count, 1): S(1, 0) = W(1, 1): S(1, 1) = W(1, 2)
N = 1
For K = 2 To UBound(W)
For Each V In Split(W(K, 2), ",")
L = Split(V, "-")
If UBound(L) = 1 Then
R = Split(L(1), ".")
L = Split(L(0), ".")
If UBound(L) = 3 And UBound(R) = 3 Then
For A = L(0) To R(0)
T(0) = A
For B = -L(1) * (A = L(0) * 1) To IIf(A < R(0) * 1, 255, R(1))
T(1) = B
For C = -L(2) * (B = L(1) * 1) To IIf(B < R(1) * 1, 255, R(2))
T(2) = C
For D = -L(3) * (C = L(2) * 1) To IIf(C < R(2) * 1, 255, R(3))
T(3) = D
N = N + 1
S(N, 0) = W(K, 1)
S(N, 1) = Join(T, ".")
Next D, C, B, A
End If
Else
N = N + 1
S(N, 0) = W(K, 1)
S(N, 1) = V
End If
Debug.Print N
Next V, K
[Sheet3!A1:B1].Resize(N).Value2 = S
End Sub

 

[BSALV]

stop and write you array to your sheet if N > +1,047,000 and restart from scratch for the next +1,047,000

 

[Danny54]

Thanks for the suggestion. I tried creating code to do that but had no luck. Everytime it started at the top of the S array


[Sheet3!A1:B1].Resize(N).Value2 = S

If N = 3000 Then
Debug.Print N
[Sheet5!A1:B1].Resize(N).Value2 = S
' N = 1
End If

If N = 4000 Then
Debug.Print N
[Sheet6!A1:B1].Resize(N).Value2 = S
'N = 1
End If


Is there a way to loop thru the S array and load new sheets based on the loop count

[Sheet3!A1:B1].Resize(N).Value2 = S <- the S array contains 1047666 lines and of course the Resize to that amount crashes


so the loop would

for i = 1 to count(S)

create 10 worksheets
loop here while count between 1 - 32000
loop here while count between 320001 - 640000
ect.....
next

Thanks

 

[BSALV]

i didn't look how your macro is done, but this can give you an idea.

Sub Demo2()
     Dim W, S$(), N&, K&, V, L$(), R$(), A%, T$(3), B%, C%, D%

     ReDim S(1 To Rows.Count, 1)
     imax = 10000000

     For i = 1 To imax
          ptr = ptr + 1
          S(ptr, 1) = i

          If ptr >= 1000000 Or i = imax Then                    'you pass the limit or the last loop
               Set sh = Sheets.Add                              'add new sheet
               sh.Range("A1").Resize(UBound(S), 2).Value = S    'copy S to that sheet
               ReDim S(1 To Rows.Count, 1)                      'clear S
               ptr = 0                                          'reset pointer
          End If

     Next

End Sub

 

[Danny54]

Sweet,

Thanks so much