Group number sequence inside a range-CodePudding

I am new to VBA and learning myself... I am facing a similar problem with this post:

CodePudding user response：

I tested this code and it worked correctly for me. I returned to the page and saw the solution from JvdV; so I thought I'd post my solution too.

Option Explicit

Private Sub Test()

Dim result$
Dim WS As Worksheet

Set WS = ThisWorkbook.Sheets("Sheet3")

result$ = Lookupsequence(WS.Range("B1:AE1"))
result$ = Lookupsequence(WS.Range("B2:AE2"))
result$ = Lookupsequence(WS.Range("B3:AE3"))

End Sub

Private Function Lookupsequence(Return_val_col As Range) As String

Dim preValue%, value%
Dim i&
Dim result$, separator$

preValue = -1
result = ""
separator = ", "
For i = 1 To Return_val_col.count
    value = CInt(Return_val_col.Cells(1, i).value)
    
    If value = 0 Then
        Exit For
    ElseIf result <> "" And value - 1 <> preValue Then
        result = result & "-" & preValue & separator & value
    ElseIf result = "" Then
        result = value
    End If
    
    preValue = value
Next

If value = 0 Then
    value = preValue
End If

result = result & "-" & value

Lookupsequence = Trim(result)

End Function

My test data in two images from columns A to AE to test the possibility of 30 item numbers