Defined Names contain autofilter ranges?

Kmanbozzy

New Member
Joined
Apr 18, 2016
Messages
16
I want to loop through all of the defined names pertaining to my sheet. That's the easy part, the issue i am encountering is that the workbook's many auto filter ranges must be stored as a defined name, which is not want i want. None of those filters were named intentionally. And the code i'm using is failing when reaching a range that's a filter. the code is commonly used i believe.
Code:
For Each nm In ActiveWorkbook.Names
on error resume next
    If nm.RefersToRange.Parent.name = ActiveSheet.name Then
            MsgBox nm.name
    End If
Next nm
Here's where it's failing. My active sheet name is ISEntry, the comparison is name of the sheet of the active defined name. it should only go to the msgbox when ISEntry = ISEntry of course, well the line nm.RefersToRange.Parent.name is failing and the resume next is there to go ahead and tell me the name that's failing. The message is "HostedPropasl!_FilterDatabase" this is telling me that the filters are being stored as defined names even though i have not intentionally defined them. How do i work around this? I ONLY want the defined names, not this filter stuff. I assume the line is failing because there is no parent name? I'm not sure.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
Untested but try
Code:
For Each nm In ActiveWorkbook.Names
On Error Resume Next
   If nm.Visible Then
      If nm.RefersToRange.Parent.Name = ActiveSheet.Name Then
         MsgBox nm.Name
      End If
   End If
Next nm
 

Kmanbozzy

New Member
Joined
Apr 18, 2016
Messages
16
I can't believe it was that simple. Is it true that those filter ranges are stored in the defined names? Are there any other named ranges not created by me in the name manger stored in the defined names area?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
If you use
Code:
Sub chk()
   Dim Nme As Name
   Dim Msg As String
   For Each Nme In ActiveWorkbook.Names
      Msg = Msg & vbLf & Nme.Name
   Next Nme
   MsgBox Msg
End Sub
It should show all the names in the workbook.
Alternatively use
Code:
Sub chk()
   Dim Nme As Name
   For Each Nme In ActiveWorkbook.Names
      Nme.Visible = True
   Next Nme
End Sub
And then you can see them all in the name Manager
 

Forum statistics

Threads
1,082,277
Messages
5,364,197
Members
400,786
Latest member
ismi88

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top