How do I tweak this code to count hidden named ranges rather than all named ranges?

[pam53154]

I have the following code which is working great to count the number of named ranges in my spreadsheet. I want to tweak it to only count the hidden named ranges. Any thoughts?

Sub CountNamedRanges()

Dim Workbook As Excel.Workbook
Dim CountOfNames As Long

Set Workbook = ActiveWorkbook
CountOfNames = Workbook.Names.Count

If CountOfNames > 10 Then
MsgBox "More than 10."
Else: MsgBox "Not more than 10."

End If

End Sub

 

[steve the fish]

Try:

[TABLE="width: 67"]
<tbody>[TR]
[TD="width: 89, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Sub   CountNamedRanges()[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim Workbook As Excel.Workbook[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim CountOfNames As Long[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim Wname as Variant[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Set Workbook = ActiveWorkbook[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]CountOfNames = 0[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]For Each Wname in Activeworkbook.Names[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]If Wname.Visible = False Then[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]CountOfNames=CountOfNames+1[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]End If            [/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Next Wname[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]If CountOfNames > 10 Then[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]MsgBox "More than 10."[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Else: MsgBox "Not more than   10."[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT][/TD]
[/TR]
</tbody>[/TABLE]

 

[pam53154]

Perfect. Many thanks.