Russk68

Well-known Member
Joined
May 1, 2006
Messages
589
Office Version
  1. 365
Platform
  1. MacOS
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?


Code:
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!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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)
 
Upvote 0
Along the lines of
Code:
   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"))
 
Upvote 0
Hi Russ, an additional possibility is to name your ranges. I think that would cut down on the long parameters.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
Oh
I obviously didn't think of that.

I will try that.

Thank you!
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top