none
Union Help RRS feed

  • Question

  • I am seeking some assistance in developing a union of sorts as my range appears to have exceeded this limitations.

    The below worked perfectly...until I added some additional cells :(

    ------------------------------------------------

    Option Explicit

    Public Function TabOrder() As Range

        Set TabOrder = [B5:M5,B7,N7,B8,N8,B9,N9,B10,N10,B11,N11,Q7:Q8,Q10:Q11,D12,I12,D13,I13,O12,T12,O13,T13,B15,J15,N15,B20:B23,E23,B24,E24,B25,E25,B26,E26,O21:O27,B31:B33,D31:D33,G31:G33,L31:L33,O31:O33,S31:S33,B36,D36,G36,B38,G41,R41,B47:O47,D49:E49,D50:O50,B55,B57,B59]

    End Function

    ------------------------------------------------

    This is what I have been playing with and need some help with :)

    Public Function TabOrder() As Range

        Dim rng1 As Range
        Dim rng2 As Range
        Dim CombRng As Range
        
        Set rng1 =  .Range("B5:M5,B7,N7,B8,N8,B9,N9,B10,N10,B11,N11,Q7:Q8,Q10:Q11,D12,D13,I12,I13,O12,O13,T12,T13,B15,D15,F15,J15,N15,P15")
        Set rng2 = .Range("B20:B23,B24,E24,B25,E25,B26,E26,O21:O27,B31:B33,D31:D33,G31:G33,L31:L33,O31:O33,S31:S33,B36,D36,G36,B38,G41,R41,B47:O47,D49:E49,D50:O50,B55,B57,B59")
        
        Set CombRng = .Range(rng1, rng2)

        Set TabOrder = (CombRng)
        
    End Function

    ------------------------------------------------

    This is my first time attempting to create union and can't seem to get this to work :(

    Any assistance or suggestions is greatly appreciated :)

    Thank you.

    Tuesday, January 24, 2017 8:37 PM

Answers

  • Hello,

    Perhaps this will do:

    Public Function TabOrder() As Range
    
         Dim rng1 As Range
         Dim rng2 As Range
         Dim CombRng As Range
         
         Set rng1 = [B5:M5,B7,N7,B8,N8,B9,N9,B10,N10,B11,N11,Q7:Q8,Q10:Q11,D12,I12,D13,I13,O12,T12,O13,T13,B15,J15,N15,B20:B23,E23,B24,E24,B25,E25,B26,E26,O21:O27,B31:B33,D31:D33,G31:G33,L31:L33,O31:O33,S31:S33,B36,D36,G36,B38,G41,R41,B47:O47,D49:E49,D50:O50,B55,B57,B59]
         Set rng2 = [B20:B23, B24, E24,B25,E25,B26,E26,O21:O27,B31:B33,D31:D33,G31:G33,L31:L33,O31:O33,S31:S33,B36,D36,G36,B38,G41,R41,B47:O47,D49:E49,D50:O50,B55,B57,B59,P10,Q11,R12,S13,T14,U15,V16,W17,X18,Y19,Z20]
         
         Set CombRng = Union(rng1, rng2)
    
         Set TabOrder = CombRng
     End Function
    

    Jan

    • Proposed as answer by Chenchen LiModerator Wednesday, January 25, 2017 2:22 AM
    • Marked as answer by rstreets2 Thursday, February 9, 2017 6:03 PM
    Tuesday, January 24, 2017 9:09 PM

All replies

  • Hello,

    Perhaps this will do:

    Public Function TabOrder() As Range
    
         Dim rng1 As Range
         Dim rng2 As Range
         Dim CombRng As Range
         
         Set rng1 = [B5:M5,B7,N7,B8,N8,B9,N9,B10,N10,B11,N11,Q7:Q8,Q10:Q11,D12,I12,D13,I13,O12,T12,O13,T13,B15,J15,N15,B20:B23,E23,B24,E24,B25,E25,B26,E26,O21:O27,B31:B33,D31:D33,G31:G33,L31:L33,O31:O33,S31:S33,B36,D36,G36,B38,G41,R41,B47:O47,D49:E49,D50:O50,B55,B57,B59]
         Set rng2 = [B20:B23, B24, E24,B25,E25,B26,E26,O21:O27,B31:B33,D31:D33,G31:G33,L31:L33,O31:O33,S31:S33,B36,D36,G36,B38,G41,R41,B47:O47,D49:E49,D50:O50,B55,B57,B59,P10,Q11,R12,S13,T14,U15,V16,W17,X18,Y19,Z20]
         
         Set CombRng = Union(rng1, rng2)
    
         Set TabOrder = CombRng
     End Function
    

    Jan

    • Proposed as answer by Chenchen LiModerator Wednesday, January 25, 2017 2:22 AM
    • Marked as answer by rstreets2 Thursday, February 9, 2017 6:03 PM
    Tuesday, January 24, 2017 9:09 PM
  • Thank you Jan. I was able to make this work. Thank you for your suggestion. Much appreciated :)
    Thursday, February 9, 2017 6:04 PM