How do I Unhide columns that are equal to a Drop Down/Cell Value?

dancran · Aug 8, 2011

Hello everyone,

I am pretty new to this whole Excel VBA, Macro business. I have a relatively large spreadsheet (couple hundred columns) and I am trying to make it easier to navigate / select what I am viewing. I am using Excel 2007 if that is important.

This spreadsheet keeps track of contract information with a customer. Every once and a while the information changes. When this occurs the new information is dubbed Change 1,2,3,etc. In my spreadsheet the "Change #" is entered in Row 6 with the new information underneath of it. What I want my VBA code/Macro to do is only show the columns that equal the text selected from a Drop Down (in cell B5) menu created using Data Validation of Row 6. So if the Drop Down selection equals a value in Row 6, I want to keep that column visible, but other columns not visible. If the Drop Down selection is blank or """" then hide nothing.

The part where it gets trickier, is that each Change requires two column entries. So when I want to show the information for one change based on the Drop Down selection, I actually want that Column PLUS the Column to the right of it.

If I need to clarify/re-word anything please let me know. I am experienced with programming, just not in VBA so I am not sure how to setup what I want to do. Any help would be appreciated!

Dan

Code:

  <table border="0" cellpadding="0" cellspacing="0" width="1216"><col style="width:48pt" span="19" width="64">  <tbody><tr style="height:15.0pt" height="20">   <td style="height:15.0pt;width:48pt" height="20" width="64">Change 1</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>   <td style="width:48pt" width="64">
</td>  </tr>  <tr style="height:15.0pt" height="20">   <td style="height:15.0pt" height="20">
</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td>
</td>   <td>
</td>  </tr>  <tr style="height:15.0pt" height="20">   <td style="height:15.0pt" height="20">
</td>   <td>
</td>   <td>Change 1</td>   <td>
</td>   <td>Change 2</td>   <td>
</td>   <td>Change 3</td>   <td>
</td>   <td> Change 4</td>   <td>
</td>   <td>Total</td>   <td>Change 1</td>   <td>
</td>   <td> Change 2</td>   <td>
</td>   <td>Change 3</td>   <td>
</td>   <td>Change 4</td>   <td>
</td>  </tr>  <tr style="height:15.0pt" height="20">   <td style="height:15.0pt" align="right" height="20">1</td>   <td>
</td>   <td>a</td>   <td>a</td>   <td>a</td>   <td>a</td>   <td>a</td>   <td>a</td>   <td>a</td>   <td>a</td>   <td>4a</td>   <td>a</td>   <td>a</td>   <td>a</td>   <td>a</td>   <td>a</td>   <td>a</td>   <td>a</td>   <td>a</td>  </tr>  <tr style="height:15.0pt" height="20">   <td style="height:15.0pt" align="right" height="20">2</td>   <td>
</td>   <td>b</td>   <td>b</td>   <td>b</td>   <td>b</td>   <td>b</td>   <td>b</td>   <td>b</td>   <td>b</td>   <td>4b</td>   <td>b</td>   <td>b</td>   <td>b</td>   <td>b</td>   <td>b</td>   <td>b</td>   <td>b</td>   <td>b</td>  </tr>  <tr style="height:15.0pt" height="20">   <td style="height:15.0pt" align="right" height="20">3</td>   <td>
</td>   <td>c</td>   <td>c</td>   <td>c</td>   <td>c</td>   <td>c</td>   <td>c</td>   <td>c</td>   <td>c</td>   <td>4c</td>   <td>c</td>   <td>c</td>   <td>c</td>   <td>c</td>   <td>c</td>   <td>c</td>   <td>c</td>   <td>c</td>  </tr>  <tr style="height:15.0pt" height="20">   <td style="height:15.0pt" align="right" height="20">4</td>   <td>
</td>   <td>d</td>   <td>d</td>   <td>d</td>   <td>d</td>   <td>d</td>   <td>d</td>   <td>d</td>   <td>d</td>   <td>4d</td>   <td>d</td>   <td>d</td>   <td>d</td>   <td>d</td>   <td>d</td>   <td>d</td>   <td>d</td>   <td>d</td>  </tr> </tbody></table>

3link · Aug 8, 2011

Edit: Oh god. I'll have to think on that one for a bit.

dancran · Aug 8, 2011

Yeah, that is how I felt when I was thrown this spreadsheet. I am just trying to make it more easy to view and "debug" if you will. The problem is I have no idea how to make a VBA program that would do this because it is more complex than the simple programs I have seen when searching for a solution.

tweedle · Aug 8, 2011

You can give this a whirl and see if I got your layout right.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 134px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD></TD><TD>Change 3</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD></TD><TD>Change 1</TD><TD>Orig 1</TD><TD>Change 2</TD><TD>Orig 2</TD><TD>Change 3</TD><TD>Orig 3</TD></TR></TBODY></TABLE>

Code would go in the Sheet node of the IDE

Code:

[COLOR=blue]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]
    If Target.Address = "$B$5" Then
        FilterVal = Range("B5").Text
        Application.ScreenUpdating = False
        For ColIdx = UsedRange.Columns.Count To 3 Step -2
            If Cells(6, ColIdx) = FilterVal Then
                Columns(ColIdx).Hidden = False
                Columns(ColIdx + 1).Hidden = False
            Else
                Columns(ColIdx).Hidden = True
                Columns(ColIdx + 1).Hidden = True
            End If
        Next
        Application.ScreenUpdating = True
    End If
End Sub

After
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 134px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD></TD><TD>Change 3</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD></TD><TD>Change 3</TD><TD>Orig 3</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

EDIT; Hmm, Total column is causing undesireable results - hadn't seen that!

3link · Aug 8, 2011

I didn't even know this was possible. I'm trying to figure out what this does:

Rich (BB code):

For ColIdx = UsedRange.Columns.Count To 3 Step -2

tweedle · Aug 8, 2011

Revised to handle the Totals:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    'If the cell changing is B5
    If Target.Address = "$B$5" Then
        FilterVal = Range("B5").Text
        'Turn off the flicker that's about to happen
        Application.ScreenUpdating = False
        
        'Loop Columns C to End
        For ColIdx = 3 To UsedRange.Columns.Count
            'If the Filter is for Total, then increment every column and hide non-Total
            If Trim(FilterVal) = "Total" Then
                If Cells(6, ColIdx) = FilterVal Then
                    Columns(ColIdx).Hidden = False
                Else
                    Columns(ColIdx).Hidden = True
                End If
            Else    'Filter something other than Total
                
                'Look for the Column Label of row 6 to match filter
                If Cells(6, ColIdx) = FilterVal Then
                    'If matched, then unhide it
                    Columns(ColIdx).Hidden = False
                    'increment the column index
                    ColIdx = ColIdx + 1
                    'Unhide that one too
                    Columns(ColIdx).Hidden = False
                Else 'not the column label we're looking for
                    'Hide 'em
                    Columns(ColIdx).Hidden = True
                    Columns(ColIdx + 1).Hidden = True
                End If
            End If
        
        Next
        'Turn the flicker back on
        Application.ScreenUpdating = True
    End If
End Sub

dancran · Aug 8, 2011

Ooook. Close. As for why it is not working 100% percent, I am getting a headache haha.

I had to change one thing to make it work for my spreadsheet ( 3 to a 6 in my for statement) and it is almost running smoothly.

I decided to remove the drop down for the time being (doesn't change anything though) and just enter the values to search for manually.

The problems that are showing up are:

If I have everything displayed (unhidden) and type in a Change #, it will return nothing. Then if I go back and type a different Change #, it will return those results correctly.

Next, if the correct columns are displaying for a certain Change # and I remove the text to make it blank/null, it will only return the totals I have throughout the spreadsheet, rather than every column.

So confused...

Example Run through:
Start with text in B5 is blank (all columns showing).
Enter Change 1 -> Results in nothing
Enter Change 2 -> Results in Change 2 Columns correctly
Enter blank -> Returns Totals throughout the spreadsheet, not all Changes
Enter Change 1 -> Results in Change 1 Columns correctly
Enter blank -> Returns all totals and changes correctly

What is goinnnn on?

tweedle · Aug 8, 2011

Used Range is literally the range being used (populated with something)

Normal For Next loops inherently increment with +1 frequency
So here we're starting at the far right, incrementing up doesn't do any good, so 'Step -2' says decrement down (the column index) by 2.

dancran · Aug 8, 2011

Tweedle,

I failed to point out in my example code, that the Totals are also done in pairs of columns, so the same layout you use for the Changes should work fine for them as well.

tweedle · Aug 8, 2011

The first version is working for you then?

How do I Unhide columns that are equal to a Drop Down/Cell Value?

dancran

New Member

Excel Facts

3link

Board Regular

dancran

New Member

tweedle

Well-known Member

3link

Board Regular

tweedle

Well-known Member

dancran

New Member

tweedle

Well-known Member

dancran

New Member

tweedle

Well-known Member

Similar threads

Forum statistics

Share this page

How do I Unhide columns that are equal to a Drop Down/Cell Value?

New Member

Excel Facts

Board Regular

New Member

Well-known Member

Board Regular

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock