VBA code to resolve overlapping page ranges

When preparing excerpts of pages from a deposition transcript for cited ranges in a court filing, it may be quite common to end up with a list of beginning and ending page ranges in two columns. It would be nice to simply take those ranges, insert a hyphen in between the beginning and ending of each range, and a comma between each individual range to set an extraction range in Adobe Acrobat, so you end up with the PDF that you need.

However, it's quite likely that in this situation you'll end up with many overlapping ranges where one range appears in the middle of the range listed before it, or the range begins in the midway point of a preceding range, but then ends several pages after it.

It's possible to easily generate a list of page ranges that account for all pages in your list, but which don't overlap and which don't include duplicates. Be sure to list the ranges in columns A and B, and then sort the data in order by the beginning pages before running the code.

After running the macro, the ranges will be listed in columns D and E:

. . . the input for the extraction in Acrobat then goes like this:

Sub ConsolidatePageRanges()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim s As Long, e As Long
    Dim nextOutputRow As Long

    ' Set the worksheet to the active sheet
    Set ws = ActiveSheet

    ' Ensure data is sorted by Start Page for this logic to work correctly
    ' You might want to manually sort Column A first if needed

    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' Check if there is data to process
    If lastRow < 2 Then
        MsgBox "No data to process (need at least one row besides header).", vbInformation
        Exit Sub
    End If

    ' Initialize the first range
    s = ws.Cells(2, 1).Value
    e = ws.Cells(2, 2).Value

    ' Set output location (e.g., Column D and E, starting from row 2)
    nextOutputRow = 2
    ws.Cells(1, 4).Value = "Consolidated Start"
    ws.Cells(1, 5).Value = "Consolidated End"

    ' Loop through the rest of the data
    For i = 3 To lastRow
        ' If the current start page is greater than the current consolidated end page,
        ' the ranges do not overlap, so write the consolidated range and start a new one.
        If ws.Cells(i, 1).Value > e Then
            ws.Cells(nextOutputRow, 4).Value = s
            ws.Cells(nextOutputRow, 5).Value = e
            nextOutputRow = nextOutputRow + 1
            s = ws.Cells(i, 1).Value
            e = ws.Cells(i, 2).Value
        Else
            ' If they overlap, extend the current consolidated end page if the new end page is greater.
            If ws.Cells(i, 2).Value > e Then
                e = ws.Cells(i, 2).Value
            End If
        End If
    Next i

    ' Write the last consolidated range
    ws.Cells(nextOutputRow, 4).Value = s
    ws.Cells(nextOutputRow, 5).Value = e

    MsgBox "Ranges consolidated successfully in columns D and E.", vbInformation

End Sub