Parse Range Return String
Ranges which are not consecutive can be returned from a Range function in a format like this:
A1,A3:B4,C5:C8,D10,E15:E40
The following code shows how to extract row ranges in purely numerical form so that the result would be:
1
3-4
5-8
10
15-40
Program Code
Public Sub PrintSelectedLabels()
' *******************************************************
' Illustrate Parsing A Range String To Extract Row Ranges
' This is a code snippet - not complete
' *******************************************************
' *******************************************************
' Get The String Of All Ranges Selected
' *******************************************************
strSelectedRange = Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False)
' *******************************************************
' Split Each Range Selected Using Comma As Delimeter
' *******************************************************
strRangeArray = Split(strSelectedRange, ",")
' *******************************************************
' Array strRangeArray Now Contains All The Parsed Ranges
' *******************************************************
' For Each Array Element, Extract The Starting And Ending
' Rows
' *******************************************************
For i = LBound(strRangeArray) To UBound(strRangeArray)
strBuildRangeStartingRow = ""
strBuildRangeEndingRow = ""
boolMultiRange = False
lngLengthOfRangeString = Len(strRangeArray(i))
For j = 1 To lngLengthOfRangeString
If Mid(strRangeArray(i), j, 1) = ":" Then
boolMultiRange = True
lngRangeStartingRow = CLng(strBuildRangeStartingRow)
strBuildRangeEndingRow = ""
GoTo GetNextCharacter
End If
' *******************************************************
' Process MultiRange When ":" Is Encountered
' *******************************************************
If boolMultiRange Then
If IsNumeric(Mid(strRangeArray(i), j, 1)) Then
strBuildRangeEndingRow = strBuildRangeEndingRow & Mid(strRangeArray(i), j, 1)
End If
GoTo GetNextCharacter
End If
' *******************************************************
' Process Before the ":" Is Encountered
' *******************************************************
If IsNumeric(Mid(strRangeArray(i), j, 1)) Then
strBuildRangeStartingRow = strBuildRangeStartingRow & Mid(strRangeArray(i), j, 1)
strBuildRangeEndingRow = strBuildRangeEndingRow & Mid(strRangeArray(i), j, 1)
End If
GetNextCharacter:
Next j
' *******************************************************
' End Of String Encountered - Complete The Process
' *******************************************************
If Not boolMultiRange Then
lngRangeStartingRow = CLng(strBuildRangeStartingRow)
lngRangeEndingRow = CLng(strBuildRangeEndingRow)
Else
lngRangeEndingRow = CLng(strBuildRangeEndingRow)
End If
If lngRangeStartingRow < 3 Then
MsgBox ("You Must Select A Starting Row Greater Than 2")
Exit For
End If
Call SendToPrinter
Next i
End Sub