Two-level column hiding

[scoha]

I have three named ranges of columns - all adjacent to each other (eg A:G, I:Z, AA:BZ)

In each group of columns there will be some columns that are used for lists and will have label data in the first row.

I want to have a macro that toggles between hiding all columns that are empty across all ranges and then three other macros which selectively display only columns that have data in each range.

How do I combine EntireColumns.Hidden=True with "sub" macros where I want to display just those within each range?

 

[villy]

Sub Hid()
if Range("H1").EntireColumns.Hidden = True Then Range("H1").EntireColumns.Hidden = False
 
End Sub

 

[Peter_SSs]

I have three named ranges of columns - all adjacent to each other (eg A:G, I:Z, AA:BZ)

How is A:G adjacent to I:Z - what happened to column H?

 

[Peter_SSs]

Assuming that your ranges are in fact adjacent, test this in a copy of your workbook.

I have also assumed that the determination of hiding and unhiding depends only on row 1 being blank or not and that the top row of the combined ranges has at least one blank cell.

I have used range names "Range1", "Range2" and "Range3". Change the code to suit your range names.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Hide_Unhide()<br>    <SPAN style="color:#00007F">Dim</SPAN> rUnion <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> lCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rUnion = Union(Range("Range1"), Range("Range2"), Range("Range3")).Rows(1)<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    lCount = rUnion.SpecialCells(xlCellTypeBlanks).SpecialCells(xlCellTypeVisible).Count<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    rUnion.SpecialCells(xlCellTypeBlanks).EntireColumn.Hidden = (lCount > 0)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>