# Excel VBA: Show/Hide rows based on dropdown value



## foxtrot11 (Mar 15, 2010)

How do I hide and show different columns based on values selected from a drop-down list? I wrote a code to do this but there has to be an easier way.

Cell C2 has a drop-down list. This list is from another part of the workbook. Next to the list in the workbook, I put values representing the beginning of the columns I want to Show.

Units 4
Intervals 5
Time 6
All 0

On the page that I am working with I have created a spreadsheet that tracks monthly amounts of Units, Intervals and Time (in separate columns). I want to be able to select "Units" from C2 and show columns, 4, 7, 10, 13... +3 each) then be able to show Intervals (and only show columns 5, 8, 11, 14... etc), then Time (and show columns 6, 9, 12, 15... etc) and if I select All, I want to show everything. 

Dim x As Integer
Dim vCriteria
vCriteria = Range("C4")

If vCriteria = "0" Then
Columns("D:AS").Select
Selection.EntireColumn.Hidden = False

Else
Columns(vCriteria).Select
Selection.EntireColumn.Hidden = False

Columns(vCriteria + 3).Select
Selection.EntireColumn.Hidden = False
Columns(vCriteria + 6).Select
Selection.EntireColumn.Hidden = False
Columns(vCriteria + 9).Select
Selection.EntireColumn.Hidden = False
'... and repeat til vCriteria + 40 (column AS)

End If
End Sub

This works. But.... 
There has to be an easier way to write this code. Additionally, I want to be able to add more more columns to this spreadsheet as it is tracking monthly numbers so I don't want to specify a fixed range of columns (ie: D:AS). 

Thanks in advance.


----------



## Keebellah (Mar 27, 2008)

Hi, welcome to the board,
Can you post a small sample with the result you want, it's easier to understand (for me) when I see it.
And, which version of Excel?


----------



## foxtrot11 (Mar 15, 2010)

Sure, here is an example with the code.
Change the list option in C2 and let me know if you have further questions.
It's working okay, I just wanted to know if there was an easier way. Especially if it wasn't as formulaic (like my example of every 3rd column). For example, what if I only wanted to show the columns that contained the value "23.00"? 
Thanks in advance!


----------



## Keebellah (Mar 27, 2008)

Will that meen that you make a selection in C2, and then another optional selection (let's say C3) where you could enter any value and then just show in the case of 23 that one column alone?


----------



## foxtrot11 (Mar 15, 2010)

Well, no. I have 2 questions.

The first, is, how do I simplify the code of _this_ spreadsheet.

The second question asks: if, in a spreadsheet, I wanted to hide certain columns based on a specific text string, what function/code would I use? This question is hypothetical and not in realtion to the attached spreadsheet. Let's say for instance that the text string I want to find is in cell A1.


----------



## Keebellah (Mar 27, 2008)

If Range("S9").value = "this text" then Range("S:S").Hidden = True


----------



## Keebellah (Mar 27, 2008)

Do you want to get rid of the 3 column test to hide or unhide?
Do you want to do it programmatically?
I think your question about hiding a column is related to the column having value 23?


----------

