VBA Help - Ungrouping Named Range Columns based on Variable

[Johnny Thunder]

Hello All,

I am working on a project that will Group/Ungroup specific columns based on a loop. for ease of maintenance I have created Named Ranges for the groupings to Expand or collapse the groupings based on a variable in a list.

Here is the list

Excel Workbook
	A	B	C
1	Divisions	Named Range	Grouping Level
2	Domestic Theatrical	DomTheat	3
3	International Theatrical	IntTheat	3
4	Domestic Syndication		3
5	Domestic Cable Distribution		3
6	International Syndication		3
7	Domestic Licensing		3
8	International Licensing		3
9	Domestic Home Video		3
10	International Home Video		3
11	Domestic Digital Distribution		3
12	International Digital Distribution		3
13	Games		3
Sheet2

I have gathered some code from a user on another forum with a similar project but this code does not seem to work and I continue to get an error:

Error 1004: Unable to Set ShowDetail property to Range Class

Sub TestHideOrShowDetail()


Dim ws As Worksheet, rng As Range


Set ws = ActiveSheet
Set rng = ws.[DomTheat]                    'Named Range "DomTheat" is columns CP:DS on my activesheet


HideOrShowDetail DetailRange:=rng, ShowDetail:=True


End Sub
Sub HideOrShowDetail(ByRef DetailRange As Range, Optional ByVal ShowDetail As Boolean = False)


Dim rngrow As Range


For Each rngrow In DetailRange.Rows
    rngrow.ShowDetail = True '--------------------------------Error line
Next rngrow


End Sub

Any ideas what the issue is?

I still need to also create a loop that will look at my list and loop thru each named range in the list. For simplicity I have only created two named ranges.

 

[Kamolga]

Hi,

I would not use an extra function for hiding details, you can make it on one line

[COLOR=#008000]'To group columns of named Range "DomTheat" from another sheet[/COLOR]    
    Application.Goto [DomTheat]
    Selection.Columns.Group
[COLOR=#008000]'To hide all details of all grouped columns of activesheet[/COLOR]
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
[COLOR=#008000]'Show details of "DomTheat"[/COLOR]
    ActiveSheet.Columns([DomTheat].Column).ShowDetail = True
[COLOR=#008000]'Hide detais of "DomTheat"[/COLOR]
    ActiveSheet.Columns([DomTheat].Column).ShowDetail = False