VBA Help - Ungrouping Named Range Columns based on Variable

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
693
Office Version
  1. 2016
Platform
  1. MacOS
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
ABC
1DivisionsNamed RangeGrouping Level
2Domestic TheatricalDomTheat3
3International TheatricalIntTheat3
4Domestic Syndication3
5Domestic Cable Distribution3
6International Syndication3
7Domestic Licensing3
8International Licensing3
9Domestic Home Video3
10International Home Video3
11Domestic Digital Distribution3
12International Digital Distribution3
13Games3
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

Code:
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi,

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

Code:
[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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top