How to select nonadjacent difiened names at once?

soroush.kalantari · #1 01-09-2024, 03:14 AM

I have written following code to select some nonadjacent difined names plus a user selected range at once (see the Excel attachment. It give error at “Range("""" & Range("range" & i).Address(0, 0) & "," & rng0.Address(0, 0) & """").Select” line. Can you give me some guides on this issue?

Sub selectnonadjacentdinfinedname()
Dim i As Integer
Dim rng As Range
Dim rng0 As Range
Set rng = Selection
Set rng0 = Range("range1")
For i = 2 To 100
Range("""" & Range("range" & i).Address(0, 0) & "," & rng0.Address(0, 0) & """").Select
Set rng0 = Selection
Next i

Range(rng, rng0).Select
End Sub

Guessed · #2 01-09-2024, 04:11 AM

I'm not sure on the purpose of this macro but your loop might work with

Code:

  For i = 2 To 100
    Range(Range("range" & i), rng0).Select
    Set rng0 = Selection
  Next i

p45cal · #3 01-09-2024, 04:57 AM

likewise not sure but maybe

Code:

  Set rng = Selection
  Set rng0 = Range("range1")

  For i = 2 To 100
    Set rng0 = Union(rng0, Range("range" & i))
  Next i
 
  Union(rng, rng0).Select

or perhaps

Code:

  Set rng = Selection
  Set rng0 = Range("range1")

  For i = 2 To 100
    Union(rng0, Range("range" & i)).Select
  Next i

soroush.kalantari · #4 01-09-2024, 08:16 PM

Quote:

Originally Posted by Guessed

I'm not sure on the purpose of this macro but your loop might work with

Code:

  For i = 2 To 100
    Range(Range("range" & i), rng0).Select
    Set rng0 = Selection
  Next i

Thank you for your reply. The purpose of this code is to select some nonadjacent defined names which has be named range1, range2 and so on plus a user selected range. (see the attached picture, in which if active cell be A5 and defined names ranges be the colored ranges, the macro is expected to select the colored ranges and A5.)
Your code don’t work like this. (it selects also the cells between defined names, for example A9)

soroush.kalantari · #5 01-09-2024, 08:19 PM

Quote:

Originally Posted by p45cal

likewise not sure but maybe

Code:

  Set rng = Selection
  Set rng0 = Range("range1")

  For i = 2 To 100
    Set rng0 = Union(rng0, Range("range" & i))
  Next i
 
  Union(rng, rng0).Select

@p45cal .Thank you very much. First code solved the problem.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Removing microsoft build-in style names - display of style names in styles gallery changed	Firebody	Word	8	03-06-2022 08:22 AM
Select entire text and then de-select certain Headings?	tq94	Word	3	02-26-2022 03:47 AM
Address sheet - I have an address sheet, I want to create and add names from a database of names.	moscowamerican	Mail Merge	1	09-12-2020 07:31 AM
matching names in column A with names in column C	bob999999	Excel	1	04-28-2013 11:21 AM
How to enter names in Resource Pool/names	pstein	Project	1	03-26-2012 07:37 AM