SOLVED

How do I select a discontinuous named range in Excel?

%3CLINGO-SUB%20id%3D%22lingo-sub-2625651%22%20slang%3D%22en-US%22%3EHow%20do%20I%20select%20a%20discontinuous%20named%20range%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2625651%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20named%20range%20that%20is%20discontinuous.%20For%20example%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3Ediscontinuous%3DSheet1!%24C%243%3A%24C%244%2CSheet1!%24D%243%3A%24D%245%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(the%20actual%20range%20in%20my%20application%20is%20more%20complicated).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20VBA%2C%20I%20can%20easily%20select%20this%20range%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EDim%20discontinuous%20As%20Range%3CBR%20%2F%3ESet%20discontinuous%20%3D%20Range(%22discontinuous%22)%3CBR%20%2F%3Ediscontinuous.Select%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20be%20able%20to%20do%20the%20same%20thing%20efficiently%20in%20Excel.%20By%20%22efficiently%2C%22%20I%20mean%20without%20all%20the%20clicking%2C%20shift%20clicking%2C%20control%20clicking%2C%20etc.%20Is%20it%20possible%3F%20It%20seems%20like%20something%20that%20should%20be%20possible%20to%20do%20in%20Name%20Manager.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2625651%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2625774%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20select%20a%20discontinuous%20named%20range%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2625774%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382386%22%20target%3D%22_blank%22%3E%40perkin_warbeck%3C%2FA%3EExcel's%20Name%20Manager%20could%20be%20used%20for%20this%2C%20just%20give%20that%20range%20a%20custom%20name%20then%20refer%20to%20it%20using%20that%20custom%20name%2C%20check%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20title%3D%22referring%20to%20cells%20using%20name%20manager%20in%20Excel%22%20href%3D%22https%3A%2F%2Fthetechnicalbooth.blogspot.com%2F2021%2F08%2Freferring-to-cells-using-name-manager.html%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ereferring%20to%20cells%20using%20name%20manager%20in%20Excel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2625777%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20select%20a%20discontinuous%20named%20range%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2625777%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382386%22%20target%3D%22_blank%22%3E%40perkin_warbeck%3C%2FA%3E%26nbsp%3B%20Excel's%20Name%20Manager%20could%20be%20used%20for%20this%2C%20just%20give%20that%20range%20of%20cells%20a%20custom%20name%20and%20refer%20to%20it%20in%20Excel's%20formulas%20(i.e.%20SUM(discontinuous))%2C%20check%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20title%3D%22referring%20to%20cells%20using%20name%20manager%20in%20Excel%22%20href%3D%22https%3A%2F%2Fthetechnicalbooth.blogspot.com%2F2021%2F08%2Freferring-to-cells-using-name-manager.html%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ereferring%20to%20cells%20using%20name%20manager%20in%20Excel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2625782%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20select%20a%20discontinuous%20named%20range%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2625782%22%20slang%3D%22en-US%22%3EI%20know%20how%20to%20refer%20to%20a%20named%20range%20in%20a%20formula.%20I%20simply%20want%20to%20select%20the%20range%20for%20a%20copy%20or%20paste.%20In%20other%20words%2C%20I%20want%20to%20do%20the%20equivalent%20of%3CBR%20%2F%3E%3CBR%20%2F%3Ediscontinuous.Select%3CBR%20%2F%3E%3CBR%20%2F%3EExcept%20I%20want%20to%20do%20it%20in%20Excel.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2626006%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20select%20a%20discontinuous%20named%20range%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2626006%22%20slang%3D%22en-US%22%3EI%20found%20the%20answer.%20In%20the%20name%20box%2C%20select%20the%20name.%20This%20automatically%20selects%20the%20range.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20expected%20to%20find%20a%20similar%20feature%20in%20Name%20Manager.%3C%2FLINGO-BODY%3E
Contributor

I have a named range that is discontinuous. For example, 

 

discontinuous=Sheet1!$C$3:$C$4,Sheet1!$D$3:$D$5

 

(the actual range in my application is more complicated).

 

In VBA, I can easily select this range:

 

Dim discontinuous As Range
Set discontinuous = Range("discontinuous")
discontinuous.Select

 

I would like to be able to do the same thing efficiently in Excel. By "efficiently," I mean without all the clicking, shift clicking, control clicking, etc. Is it possible? It seems like something that should be possible to do in Name Manager.

 

 

3 Replies

@perkin_warbeck  Excel's Name Manager could be used for this, just give that range of cells a custom name and refer to it in Excel's formulas (i.e. SUM(discontinuous)), check this:

 

Referring to cells using Name Manager in Excel  

I know how to refer to a named range in a formula. I simply want to select the range for a copy or paste. In other words, I want to do the equivalent of

discontinuous.Select

Except I want to do it in Excel.
best response confirmed by perkin_warbeck (Contributor)
Solution
I found the answer. In the name box, select the name. This automatically selects the range.

I expected to find a similar feature in Name Manager.