Excel VBA combining sequential numbers

Question

I never went to the forum before, so I apologize if I break any etiquette in the forum. I tried to find this forum and a general internet search for this answer, but cannot find what I am looking for.

I have two columns of numbers, both formatted as # - #. In fact, the first number is the chapter number, and the second is the section number. If the sections in both columns are a sequential range, I need to combine it. For instance:

2-16 | 2-31
2-17 | 2-32
2-18 | 2-33
2-30 | 2-55

It would be:

2-16--2-18 | 2-31--2-33
2-30       | 2-55

Sorry for the formatting. I tried to delimit a hyphen and write code that checks all four lines at once, but I cannot figure out how to check this through sections to find when they stop being sequential. Any help is appreciated!

Answer 1

, , - . , , , , .

Private Sub Combine()
    Dim i, j, m, n As Integer
    Dim LookStart, SvaeStart As Range
        'Assume the data stored in column A, Cells A2:A9
        'Put the results in Column B,starting with cell B2
    Set LookStart = Range("A2")
    Set SaveStart = Range("B2")
    i = 0
    j = FindEnd(LookStart)
    m = 0
    Do
        n = FindNext(LookStart.Offset(m, 0))
        SaveStart.Offset(i, 0).Value = LookStart.Offset(m, 0).Value
        If n <> 0 Then
            SaveStart.Offset(i, 0).Value = SaveStart.Offset(i, 0).Value & _
                                           " -- " & LookStart.Offset(m + n, 0).Value
        End If
        m = m + n + 1
        i = i + 1
    Loop While m <= j
End Sub

Private Function FindEnd(ByVal start As Range) As Integer
    FindEnd = Range("A1").Offset(Cells.Rows.Count - 1, _
                          start.Column - 1).End(xlUp).Row - start.Row
End Function

Private Function FindNext(ByVal start As Range) As Integer
    Dim i, j, flag, CurrentNum, LastNum As Integer
    Dim CurrentText As String
    i = 0
    j = FindEnd(start)
    flag = 0
    Do
        CurrentText = start.Offset(i, 0).Text
        If i <> 0 Then
            If LastNum + 1 <> Left(CurrentText, Application.WorksheetFunction.Find("-", _
                          CurrentText) - 1) * 100 + Right(CurrentText, 2) Then flag = 1
        End If
        LastNum = Left(CurrentText, Application.WorksheetFunction.Find("-", _
                       CurrentText) - 1) * 100 + Right(CurrentText, 2)
        i = i + 1
    Loop While flag = 0 And i <= j
    If flag = 0 Then FindNext = i - 1
    If flag = 1 Then FindNext = i - 2
End Function

:

A | B

2-16 | 2-16 - 2-18

2-17 | 2-30

2-18 | 2-32 - 2-34

2-30 | 2-56 - 2-58

2-32 | 2-60

2-33

2-34

2-56

2-57

2-58

2-60

---

- ? !

, !

Answer 2

, - "ChaptersAndSections"..

For Each rCol In Range("ChaptersAndSections").Columns
    With Range(rCol.Address)
        For r = 1 To .Rows.Count
            sValue = .Rows(r).Value
            sValue2 = .Rows(r).Value
            If Not r + 1 > .Rows.Count Then
                Do While CInt(Mid(.Rows(r + 1).Value, InStrRev(.Rows(r + 1).Value, _
                  "-") + 1)) = CInt(Mid(sValue2, InStrRev(sValue, "-") + 1)) + 1
                    sValue2 = .Rows(r + 1).Value
                    .Rows(r + 1).Delete shift:=xlUp
                Loop
            End If
            If sValue <> sValue2 Then .Rows(r).Value = sValue & "--" & sValue2
        Next r
    End With
Next rCol

, , , , .