Results 1 to 4 of 4

Thread: Convert Series into Range with specified count

  1. #1
    Member ayazgreat's Avatar
    Join Date
    Mar 2012
    Posts
    86
    Rep Power
    13

    Convert Series into Range with specified count

    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


    Code:
    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
    Attached Files Attached Files
    Somthing is better than nothing

  2. #2
    Member ayazgreat's Avatar
    Join Date
    Mar 2012
    Posts
    86
    Rep Power
    13
    Could anybody please help to resolve it ?
    Somthing is better than nothing

  3. #3
    Member
    Join Date
    Jun 2013
    Posts
    93
    Rep Power
    12
    do you think you provided sufficient explanation for your problem?
    furthermore the current code does not work with removed data
    Last edited by patel; 11-09-2013 at 12:38 PM.

  4. #4
    Member ayazgreat's Avatar
    Join Date
    Mar 2012
    Posts
    86
    Rep Power
    13
    yes patel I have provided sufficient explanation regarding to my problem , where is confusion ? furthermore I will update complete data workbook.
    Somthing is better than nothing

Similar Threads

  1. Replies: 2
    Last Post: 07-06-2013, 05:59 PM
  2. Count consecutive value in a range using formula
    By LalitPandey87 in forum Excel Help
    Replies: 6
    Last Post: 11-12-2012, 05:23 PM
  3. Convert Series into Range.
    By ayazgreat in forum Excel Help
    Replies: 13
    Last Post: 03-26-2012, 08:49 PM
  4. Unique Count on a Filtered Range
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 08-14-2011, 04:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •