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.

Solved: Excel 2007 Auto Hide/Unhide Rows

Discussion in 'Business Applications' started by rendi, Aug 12, 2010.

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

    rendi Thread Starter

    Joined:
    Aug 12, 2010
    Messages:
    7
    I will eventually want this to work on all worksheets in the workbook as their data may change as the "linked tables" are refreshed. The linked tables are from Access.

    What I want to do is look in column Q to see if the result of the calculation in that cell is 0. If so, I want the row hidden. I don't want to hide empty rows, just the one where the calculation =0. If the data in the workbook, i.e. the linked tables are updated, then I want any rows that have changed from 0 to another value to then become unhidden.

    I have been searching for VBA code to auto hide/unhide rows in Excel 2007 based on the value in a specific column. I found the following code, but can't seem to get it to work quite right. I changed some of the criteria to fit my worksheet. The problem is that it hides all rows that are empty even though I changed the criteria from "" to 0. Also, this does not unhide rows that may refresh with a value. :confused:

    I would really appreciate some guidance. My Access and Excel skills are pretty good, but when it comes to incorporating VBA into the mix, I get a little lost.



    Sub HideEmptyRows()
    'This macro hides all of the rows which are empty, for printing.
    'created by Geoff Faulkner 12-29-2004

    'Declare variables
    Dim intStartRow
    Dim intEndRow
    Dim intTargetColumn
    Dim intCounter

    'set default values
    intStartRow = 6
    intEndRow = 2000
    intTargetColumn = 17

    'cycle through each row in the range
    For intCounter = intStartRow To intEndRow Step 1
    'if the cell contains no value
    If Cells(intCounter, intTargetColumn).Value = " " Then
    'Select the row
    Rows(intCounter).Select
    'hide the row
    Selection.EntireRow.Hidden = False

    If Cells(intCounter, intTargetColumn).Value = 0 Then
    'Select the row
    Rows(intCounter).Select
    'hide the row
    Selection.EntireRow.Hidden = True
    End If
    End If
    Next
    End Sub
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,531
    First Name:
    Hans
    I editted your macro and put it in the attached sample

    two buttons to demonstrate, I changed the range from 6 to 12 or something to show.

    Just edit to your needs
    :D
     

    Attached Files:

  3. rendi

    rendi Thread Starter

    Joined:
    Aug 12, 2010
    Messages:
    7
    Hans,

    Thank you so much, this is perfect!!! :D

    The only change I made other than the end row # was using a keyboard shortcut instead of the button and I needed to put a value in the cells that were empty in column Q that has a title in column A. One last question, however, with future worksheets in this workbook, do I have to add the module to each worksheet or will this work for any worksheet that happens to be active?

    Kendra
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,531
    First Name:
    Hans
    The module is only active in the sheet it's in,
    If you need it to be valid for any open worksheet you will have to put it the the Persona.xls file which is created when you store macro's in there.
    Notice that when you record a macro it asks where you want to store it, if you say teh Personal macro book it will place it there, it's a hidden sheet (someting like the normal.dot in word) but once you have one macro in it, you can access the vba project via de vba editor and just copy any code you want to the personal.xls vba project sheet, then it's there for every sheet.

    I hope I have been able to explain it in an understanding way.

    One hint, if you do this make sure you add some test so that the macro won't be excecuted by accident on any sheet.
     
  5. rendi

    rendi Thread Starter

    Joined:
    Aug 12, 2010
    Messages:
    7
    Got it. Thanks again for all your help!
     
  6. 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/942597

  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