Results 1 to 1 of 1

Thread: Return individual page numbers from a list of pages and ranges (e.g., 1,5,9-12,20-18)

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13

    Return individual page numbers from a list of pages and ranges (e.g., 1,5,9-12,20-18)

    I know this won't come up often but, when it does, I think you will find this function useful. The title for this thread pretty much says it all... the function takes a list of pages and/or page ranges as a text string and expands that list into a list of individual pages. If a dashed range is presented "backwards" (as in the 20-18 part of my example), then the individual pages for it are returned in declining numerical order (20, 19, 18). This function is most useful when called from other VB code as it returns the expand list as an array which can be iterated by your calling code.

    Code:
    Function PagesToPrint(sInput As String) As Variant
      Dim X As Long, Z As Long, Temp As String, sNumbers() As String, sRange() As String
      If sInput Like "*# #*" Then GoTo Bad
      sInput = Replace(sInput, " ", "")
      If sInput Like "*[!0-9,-]*" Or sInput Like "*[,-][,-]*" Or _
         Not sInput Like "*#" Or Not Val(sInput) Like "[1-9]*" Then GoTo Bad
      sNumbers = Split(sInput, ",")
      For X = 0 To UBound(sNumbers)
        If sNumbers(X) Like "*-*" Then
          If sNumbers(X) Like "*-*-*" Then GoTo Bad
          sRange = Split(sNumbers(X), "-")
          sNumbers(X) = ""
          For Z = sRange(0) To sRange(1) Step Sgn(sRange(1) - sRange(0) + 0.1)
            sNumbers(X) = sNumbers(X) & "," & Z
          Next
          sNumbers(X) = Mid(sNumbers(X), 2)
        Else
          sNumbers(X) = Val(sNumbers(X))
        End If
      Next
      PagesToPrint = Split(Join(sNumbers, ","), ",")
      Exit Function
    Bad:
      PagesToPrint = Array()
      MsgBox """" & sInput & """" & vbLf & vbLf & "The specified range of values is incorrectly formed!", vbCritical
    End Function
    If, for any reason, you would rather see the list returned as a comma delimited text string, then simply change this line of code...

    Code:
    PagesToPrint = Split(Join(sNumbers, ","), ",")
    to this...

    Code:
    PagesToPrint = Join(sNumbers, ",")
    If you plan to call this modified function as a UDF (user defined function), then you should insert this line of code right before the MsgBox statement at the end of the code in order to suppress the MessageBox when an erroneous argument is passed into the function.

    Code:
      If TypeOf Application.Caller Is Range Then Exit Function
    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use PagesToPrint just like it was a built-in Excel function. For example,

    =PagesToPrint(A1)

    If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
    Last edited by Rick Rothstein; 01-28-2015 at 10:31 AM.

Similar Threads

  1. Replies: 6
    Last Post: 05-20-2013, 10:06 PM
  2. List all the name ranges in an excel sheet with scope
    By LalitPandey87 in forum Excel Help
    Replies: 4
    Last Post: 03-28-2012, 07:27 AM
  3. Executing XLSM file on a web page
    By Rasm in forum Excel Help
    Replies: 1
    Last Post: 12-17-2011, 05:38 AM
  4. Capture values from IE page
    By maruthi in forum Excel Help
    Replies: 6
    Last Post: 11-22-2011, 08:25 AM
  5. List Unique/Common Values From Two Ranges
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 09-16-2011, 08:34 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •