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.

Macro to Hide/Unhide Rows Based on Results in Column Equation

Discussion in 'Business Applications' started by jwhite0720, Nov 4, 2011.

Thread Status:
Not open for further replies.
  1. jwhite0720

    jwhite0720 Thread Starter

    Joined:
    Nov 4, 2011
    Messages:
    1
    Hi there! I'm new to using these forums, but have spent some time scouring them from time to time to look for simple solutions to any excel/VBA problems I'm having. I consider myself relatively intermediate to advanced in Excel as far as formulas and spreadsheet creation goes, however VBA is foreign to me, and an area I'd love to learn. If any of you have recommendations on a good resource to begin learning VBA, please send it my way. And now....onto my macro question.

    I've got a workbook with many tabs, and one or two input tabs feed information into another summary tab using various lookup and index formulas. There are any where from 40 to 80 rows to be qualified depending on the specific summary sheet (therefore I will likely need the macro to reference only the active sheet as it needs to work the same way on different sheets independent of each other). What I would like to do is if the result of the formula in column A of the summary sheet is "----------" (starts at A7) then I would like to hide that row, and if the data is changed on the input tab to result in anything other than the qualifying "----------", for that row to be unhidden. If this can be done dynamically that'd be great, however, if a button needs to be put into the spreadsheet to execute the macro, that will be fine too.

    Thanks so much in advance for your help, and I promise I'll do my best to begin learning VBA so I can help contribute in return!
     
  2. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    HI,

    Here is a code that may help you. You can copy and paste it too a module.
    When you copy it to a module you can assign a "Short" cut key to activate the code.
    To do this you select "Tool", "Macro" and when you see the name of the macro you want to run you can select it and then select "Options" and assign the short cut key. You will be able to use the short cut key on any active sheet.

    Code:
    Sub Hide_Row()
    Application.ScreenUpdating = False
    Dim Mysh As Worksheet
    Dim cel As Range
    Set Mysh = ActiveSheet
    For Each cel In Mysh.Range("A1:A" & Mysh.Range("A65536").End(xlUp).Row)
        If Not (Mysh.Cells(cel.Row, 1).Value Like "--*") Then
            Mysh.Cells(cel.Row, 1).EntireRow.Hidden = False
        Else
            Mysh.Cells(cel.Row, 1).EntireRow.Hidden = True
        End If
    Next
    End Sub
    
     
  3. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
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/1025426

  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