View Single Post
  #6  
Old 06-23-2009, 09:40 PM
Krishnakumar Krishnakumar is offline
Forum Contributor
 
Join Date: 19 Feb 2005
Location: Gurgaon,India
Posts: 137
Krishnakumar has been very helpful
Re: Culling consecutive numbers into ranges

Hi,

Code:
Sub kTest()
Dim a, i As Long, w(), n As Long, r As Long

r = Range("a" & Rows.Count).End(xlUp).Row
With Range("b2")
    .Resize(r - 1).FormulaR1C1 = "=(rc[-1]-r[-1]c[-1]=1)+0"
    a = .Offset(, -1).Resize(r - 1, 2).Value
    .Resize(r).ClearContents
    ReDim w(1 To UBound(a, 1), 1 To 1)
    n = 1
    For i = 1 To UBound(a, 1)
        If i = 1 Then w(n, 1) = a(i, 1)
        If i > 1 Then
            If a(i, 2) = 0 Then
                w(n, 1) = w(n, 1) & "-" & a(i - 1, 1)
                n = n + 1: w(n, 1) = a(i, 1)
            End If
            If (i = UBound(a, 1)) * (a(i, 2) <> 0) Then w(n, 1) = w(n, 1) & "-" & a(i, 1)
        End If
    Next
    .Resize(n).Value = w
End With
End Sub
Note: Code edited.


HTH
__________________
Kris
God's Own Country

Last edited by Krishnakumar; 06-24-2009 at 10:43 AM. Reason: A line added
Reply With Quote