VBA question

[Russk68]

Hi All
I am using this code and the target range represents 1 layout of 72 cells. I need to include 23 more layouts. My question: should I cram it all in 1 target range or should I duplicate this code for each layout?

If Not Intersect(Target, Range("C5,E5,G5,I5,K5,M5,C9,E9,G9,I9,K9,M9,C13,E13,G13,I13,K13,M13,C17,E17,G17,I17,K17,M17,C21,E21,G21,I21,K21,M21,C25,E25,G25,I25,K25,M25,C29,E29,G29,I29,K29,M29,C33,E33,G33,I33,K33,M33,C37,E37,G37,I37,K37,M37,C41,E41,G41,I41,K41,M41,C45,E45,G45,I45,K45,M45,C49,E49,G49,I49,K49,M49")) Is Nothing Then
        If Len(Target.Value) = 0 Then
            Application.EnableEvents = False
                Target.Value = "Spare"
            Application.EnableEvents = True
        End If

Thank you!

 

[Fluff]

You won't be able to put it all into a single range reference. So either duplicate the code, or use Union to combine various ranges together.

 

[Russk68]

Hey Fluff

I just discovered that 67 cell references is the limit.

Can you show me how to combine ranges together?

(Love that code you did for me a while back)

 

[Fluff]

Along the lines of

   Set Rng = Range("C5,E5,H5,J5,L5,N5,C9,E9,H9,J9,L9,N9,C13,E13,H13,J13,L13,N13,C17,E17,H17,J17,L17,N17,C21,E21,H21,J21,L21,N21,C25,E25,H25,J25,L25,N25,C29,E29,H29,J29,L29,N29,C33,E33,H33,J33,L33,N33,C37,E37,H37,J37,L37,N37,C41,E41,H41,J41,L41,N41,C45")
   Set Rng = Union(Rng, Range("D5,F5,H5,J5,L5,N5,D9,F9,H9,J9,L9,N9,D13,F13,H13,J13,L13,N13,D17,F17,H17,J17,L17,N17,D21,F21,H21,J21,L21,N21,D25,F25,H25,J25,L25,N25,D29,F29,H29,J29,L29,N29,D33,F33,H33,J33,L33,N33,D37,F37,H37,J37,L37,N37,D41,F41,H41,J41,L41,N41,D45"))

 

[Russk68]

Works great!

Thank you!

 

[gravanoc]

Hi Russ, an additional possibility is to name your ranges. I think that would cut down on the long parameters.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Russk68]

Oh
I obviously didn't think of that.

I will try that.

Thank you!

 

[Russk68]

Hi gravanoc

I'm on a Mac and I just tried to use a named range but Excel only allows 20 cell addresses. I need 1728 cell addresses. Not sure which way to go but I appreciate the suggestion.

Thank you!