Convert Series into Range with specified count

[ayazgreat]

Hi Kris
How are you ? I need your help for following macro coded by you , actually this macro convert all different series into different range breakups and now I want to convert series into range maximum equal 50000 and I am attaching here sample workbook with your previous code and example result from column H to J.

Due to heavy size of file I have removed some series from column A


Sub kTest()
Dim a, i As Long, j As Long, w(), c As Long
a = Range("a2", Range("a" & Rows.Count).End(xlUp))
ReDim w(1 To UBound(a, 1), 1 To 3)
For i = 1 To UBound(a, 1)
If i = 1 Then
j = j + 1: w(j, 1) = a(i, 1)
Else
If a(i, 1) - a(i - 1, 1) = 1 Then
c = c + 1
Else
w(j, 2) = a(i - 1, 1): w(j, 3) = c + 1
c = 0: j = j + 1: w(j, 1) = a(i, 1)
End If
If i = UBound(a, 1) Then w(j, 2) = a(i, 1): w(j, 3) = c + 1: Exit For
End If
Next
With Range("d2")
.Resize(j, 3).Value = w
End With
End Sub


Thanks in advance

[ayazgreat]

Could anybody please help to resolve it ?

[patel]

do you think you provided sufficient explanation for your problem?
furthermore the current code does not work with removed data

[ayazgreat]

yes patel I have provided sufficient explanation regarding to my problem , where is confusion ? furthermore I will update complete data workbook.