Split function by comma and hyphen simultaneously

Guessed · #1 08-15-2021, 04:02 AM

Before you create nSplitItem, you should call a function to tidy the string and expand the number ranges. Something like this should work

Code:

Sub TestFun()
  MsgBox funExpandDashes("1-3,5,10-15")
End Sub

Function funExpandDashes(strPage As String) As String
  Dim arrOuter() As String, iMin As Integer, iMax As Integer, arrInner() As String, sExpand As String
  Dim x As Integer, y As Integer

  strPage = Replace(strPage, " ", "")     'make sure there are no spaces
  arrOuter = Split(strPage, ",")
  For x = LBound(arrOuter) To UBound(arrOuter)
    arrInner = Split(arrOuter(x), "-")
    If UBound(arrInner) > LBound(arrInner) Then
      sExpand = arrInner(0)
      For y = CInt(arrInner(0)) + 1 To CInt(arrInner(1))
        sExpand = sExpand & "," & y
      Next y
      arrOuter(x) = sExpand
    End If
  Next x
  funExpandDashes = Join(arrOuter, ",")
  
  'Add code here to make sure order is increasing (ie sort array)
  'Add code here to remove duplicates
 
End Function

Note the two suggestions to make this more robust and reduce user data entry issues

laith93 · #2 08-15-2021, 04:36 AM

Quote:

Originally Posted by Guessed

Before you create nSplitItem, you should call a function to tidy the string and expand the number ranges. Something like this should work

Thank you Mr. Guessed for the second reply to me
Mr. Guessed, excuse me I'm just a beginner in VBA, and the code is not my own development, I just googled and found it.
However, I pasted your function as follows:

Code:

Sub TestFun()
  MsgBox funExpandDashes("1-3,5,10-15")
End Sub

Function funExpandDashes(strPage As String) As String
  Dim arrOuter() As String, iMin As Integer, iMax As Integer, arrInner() As String, sExpand As String
  Dim x As Integer, y As Integer

  strPage = Replace(strPage, " ", "")     'make sure there are no spaces
  arrOuter = Split(strPage, ",")
  For x = LBound(arrOuter) To UBound(arrOuter)
    arrInner = Split(arrOuter(x), "-")
    If UBound(arrInner) > LBound(arrInner) Then
      sExpand = arrInner(0)
      For y = CInt(arrInner(0)) + 1 To CInt(arrInner(1))
        sExpand = sExpand & "," & y
      Next y
      arrOuter(x) = sExpand
    End If
  Next x
  funExpandDashes = Join(arrOuter, ",")
  End Function
  
  'Add code here to make sure order is increasing (ie sort array)
  Sub DeleteMultiplePages()

'Delete Multiple Pages by displaying Inputbox

  Dim objRange As range
  Dim strPage As String
  Dim objDoc As Document
  Dim nSplitItem As Long

  Application.ScreenUpdating = False
 
  ' Initialize and enter page numbers of pages to be deleted.
  Set objDoc = ActiveDocument
  strPage = InputBox("Enter the page numbers of pages to be deleted: " & vbNewLine & _
            "use comma to separate numbers", "Delete Pages", "For example: 1,3")
  nSplitItem = funExpandDashes

  ' Find specified pages and highlight their contents.
  For nSplitItem = nSplitItem To 0 Step -1
    With ActiveDocument
      Selection.GoTo What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=Split(strPage, ",")(nSplitItem)
      Set objRange = .Bookmarks("\Page").range
      objRange.Delete
    End With
  Next nSplitItem
 
  Application.ScreenUpdating = True
End Sub

  'Add code here to remove duplicates
 
End Sub

In conclusion, I faced a problem, so how to call a function in VBA?
Thank you for sharing your knowledge.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mistake in the help file for String "Split Function"?	John 4	Word VBA	6	11-19-2020 06:02 AM
Hyphen until the end of the row	abc3132	Word	13	10-31-2019 04:36 AM
Split function in Excel (split the screen)	Officer_Bierschnitt	Excel	1	07-05-2017 07:02 AM
Split function not working as I expect??!!	XmisterIS	Word VBA	9	04-08-2014 04:41 PM
Cross-reference function to update bullet header and number simultaneously	dljenks	Word	1	01-03-2014 01:38 PM