How can I capture the individual Excel worksheet ranges from a multiple sheet range reference string?

Question

I have a string passed to a VBA sub, which is of the format "Sheet1:Sheet5!A1:D10", referring to a range across several worksheets in the same workbook (Sheet2, Sheet3 and Sheet4 are between Sheet1 and Sheet5).

The problem I have is that I cannot apply this reference to a range variable, which as far as I can tell is due to the multiple references, and I cannot use application.union since the ranges are on different sheets.

How can I extract from this string the five individual ranges of Sheet1!A1:D10, Sheet2!A1:D10 and so on?

RocketDonkey RocketDonkey · Accepted Answer · 2012-11-14T16:53:18

This will get you the string representations of each one and then store in an array. It makes a few assumptions (that the sheets exist, that the first one listed is first in the index order, that you want everything between the two with regards to index. etc.), but it may work for your situation:

Sub Parse()

    Dim wbk As Workbook
    ' Arbitrary array size of 10 to store the ranges (as an example)
    Dim myRanges(10) As Variant

    Set wbk = ActiveWorkbook

    ' Split the string at the !
    s = "Sheet1:Sheet3!A1:D10"
    s = Split(s, "!")

    ' Range is the second element of the split (A1:D10)
    TargetRange = s(1)

    ' The first contains the sheets, so split on the :
    SheetNames = Split(s(0), ":")

    ' These sheets are the lower/upper bounds, so use their indices in a loop
    ' that cycles over the sheets in between them (inclusive)
    j = 0 ' This is for the array - you may not need it
    For i = wbk.Sheets(SheetNames(0)).Index To wbk.Sheets(SheetNames(1)).Index
      ' Range is the concatenation of the sheet at this index and the target range
      Set Rng = Range(wbk.Sheets(i).Name & "!" & TargetRange)
      ' Drop it in to our array (or handle how you want)
      myRanges(j) = Rng
      j = j + 1
    Next i

End Sub

How can I capture the individual Excel worksheet ranges from a multiple sheet range reference string?

2 Answers