1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel VBA: Show/Hide rows based on dropdown value

Discussion in 'Business Applications' started by foxtrot11, Mar 15, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. foxtrot11

    foxtrot11 Thread Starter

    Joined:
    Mar 15, 2010
    Messages:
    3
    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.
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,507
    First Name:
    Hans
    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?
     
  3. foxtrot11

    foxtrot11 Thread Starter

    Joined:
    Mar 15, 2010
    Messages:
    3
    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!
     

    Attached Files:

  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,507
    First Name:
    Hans
    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?
     
  5. foxtrot11

    foxtrot11 Thread Starter

    Joined:
    Mar 15, 2010
    Messages:
    3
    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.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,507
    First Name:
    Hans
    If Range("S9").value = "this text" then Range("S:S").Hidden = True
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,507
    First Name:
    Hans
    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?
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/910310

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice