+ Reply to Thread
Results 1 to 8 of 8

Auto-hide columns / rows when Cell Values = 0

  1. #1
    Registered User
    Join Date
    05-08-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    7

    Auto-hide columns / rows when Cell Values = 0

    Hi Guys

    I am trying to design a spreadsheet where columns on one sheet (rows on another sheet) are hidden/unhidden based on the value in the cell at the top (or far left) of the column (or row). I want this to run quickly and automatically when I change data in the spreadsheet. Each sheet is about 300 cells by 300 cells. I started using this simple formula:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = True
    Sheets("Sheet1").Select
    Range("A1:A300").Select
    For Each cell In Selection
    If cell = 1 Then
    Range(cell.Address).EntireRow.Hidden = True
    End If
    Next
    End Sub

    It was working as a normal macro but when I changed it to Autorun (Application.ScreenUpdating = True) it works but does not let me update the spreadsheet as every time I move cell it re-runs!

    The above was only a test and I actually want the rows/columns to hide when the corresponding value is 0 (it is never blank) and stay visible when the value is 1 or greater. Has anyone got any ideas?

    I had seen this on the site but couldn’t get it to work as my VB skills aren’t sufficient

    Sub watersev1()
    Dim myrange, cl, c, i As Long, x
    Application.ScreenUpdating = False
    Set c = [aj1]: Set myrange = Range([aj1], Cells(Rows.Count, "AJ").End(xlUp))
    For Each cl In myrange
    If cl.Value = "" Then Set c = Union(c, cl)
    Next: x = Split(Replace(Split(c.Address, "$AJ$1,")(1), "$AJ$", ""), ",")
    For i = 1 To UBound(x)
    If InStr(1, x(i), ":") = 0 Then x(i) = x(i) & ":" & x(i)
    Next
    Range(Join(x, ",")).EntireRow.Hidden = True: Rows(1).Hidden = IIf([aj1] <> "", False, True)
    Application.ScreenUpdating = True: Set c = Nothing: Set myrange = Nothing: End Sub

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Auto-hide columns / rows when Cell Values = 0

    Hi Todd,

    You can turn off the events when the macro starts and turn them back on when the macro finishes

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    05-08-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Auto-hide columns / rows when Cell Values = 0

    Now I'm really confused. When back into a test spreadsheet and input the simpler version and it doesn't appear on the list

    Macro.JPG

    I am pretty new to all this so any help would be appreciated...

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Auto-hide columns / rows when Cell Values = 0

    It's a Worksheet_Change event you have to put it in the relevant sheet - and it won't show. Just like the Worksheet_SelectionChange event that you posted

    Please Login or Register  to view this content.
    Last edited by xladept; 05-29-2013 at 09:42 PM.

  5. #5
    Registered User
    Join Date
    05-08-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Auto-hide columns / rows when Cell Values = 0

    Right...got it. So I have input what was suggested but the removal of ByVal seemed to stop it working. As a result I used the below:

    Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    Sheets("Sheet1").Select
    Range("A1:A300").Select
    For Each cell In Selection
    If cell = 1 Then
    Range(cell.Address).EntireRow.Hidden = True
    End If
    Next
    Application.EnableEvents = True
    End Sub

    This still gives me the problem that any time I click anywhere it runs the macro and leaves the range A1:A300 selected. Can I get the macro to run without it doing this? Basically I want the user to almost not realise that it is happening as they use the sheet.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Auto-hide columns / rows when Cell Values = 0

    Hi Todd,

    You can get rid of the selection:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-08-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Auto-hide columns / rows when Cell Values = 0

    Thanks for your help xsadept that is working. The problem is that maybe I didn't make it clear before but if a cell was a 1 and so was hidden but then changes to 0 due to other changes on the sheet then I would like the row/column to become unhidden. I changed to

    Please Login or Register  to view this content.
    The problem is that is buzzes around a lot and takes a while to update. Is there a better way that is more user friendly?

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Auto-hide columns / rows when Cell Values = 0

    Maybe:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1