selecting all named ranges at same time

E

Evagrius Ponticus

Well-known Member

Joined

May 24, 2007

Messages

1,467

• Oct 31, 2009

• #2

Well, one way would be to name all the named ranges, and then select that name.

 

Last edited: Oct 31, 2009

Upvote 0

D

drawworkyhome

Board Regular

Joined

Oct 26, 2009

Messages

58

• Oct 31, 2009

• #3

sorry, i forgot to state that i would like to use vba to do this.

 

Upvote 0

sijpie

Well-known Member

Joined

Nov 1, 2008

Messages

4,275

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Oct 31, 2009

• #4

The workbook object must have a collection of named ranges. Open the object thingy in the vba editor to check under workbook.

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Oct 31, 2009

• #5

Try

Code:

Sub SelectAllNames()
Dim i As Integer, r As Range, Bigr As Range, s As String
For i = ThisWorkbook.Names.Count To 1 Step -1
    s = ThisWorkbook.Names(i).RefersTo
    If InStr(s, ActiveSheet.Name) <> 0 Then
        Set r = Range(s)
        If Bigr Is Nothing Then
            Set Bigr = r
        Else
            Set Bigr = Application.Union(Bigr, r)
        End If
    End If
Next i
Bigr.Select
End Sub

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Oct 31, 2009

• #6

By the way, if you just want to see the named ranges, reduce the zoom level to 39% or less.

 

Upvote 0

D

drawworkyhome

Board Regular

Joined

Oct 26, 2009

Messages

58

• Oct 31, 2009

• #7

it works!
thank you for your help!
hope the weather is ok over there.
erik

 

Upvote 0

E

Evagrius Ponticus

Well-known Member

Joined

May 24, 2007

Messages

1,467

• Oct 31, 2009

• #8

VoG - great code!

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Oct 31, 2009

• #9

BlackTiger said:

VoG - great code!

Click to expand...

Thank you. This is more efficient

Code:

Sub SelectAllNames()
Dim i As Integer, Bigr As Range, s As String
For i = 1 To ThisWorkbook.Names.Count
    s = ThisWorkbook.Names(i).RefersTo
    If InStr(s, ActiveSheet.Name) <> 0 Then
        If Bigr Is Nothing Then
            Set Bigr = Range(s)
        Else
            Set Bigr = Union(Bigr, Range(s))
        End If
    End If
Next i
If Not Bigr Is Nothing Then Bigr.Select
End Sub

 

Upvote 0

D

drawworkyhome

Board Regular

Joined

Oct 26, 2009

Messages

58

• Oct 31, 2009

• #10

im curious,
how long have you been coding?
i just starting and seems like a huge undertaking.

 

Upvote 0