+ Reply to Thread
Results 1 to 8 of 8

Auto-hide columns / rows when Cell Values = 0

Hybrid View

  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

    Sub Worksheet_Change(Target As Range)
    
    Application.EnableEvents=False
    .
    .
    .
    .
    .
    Application.EnableEvents=True
    End Sub
    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

    Sub Worksheet_Change(Target As Range)
    
    Application.EnableEvents=False
    .
    Call Hide
    .
    Call Hide_Columns
    .
    Application.EnableEvents=True
    End Sub
    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:

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

  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

    Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    
    For Each cell In Sheets("Sheet1").Range("A1:A300")
    If cell = 1 Then
    cell.EntireRow.Hidden = True
    End If
    If cell <> 1 Then
    cell.EntireRow.Hidden = False
    End If
    Next
    Application.EnableEvents = True
    End Sub
    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:

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

+ 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