Results 1 to 10 of 10
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HIDE ROW IF ZERO (EXCEL 2003)

    A corrected version of your macro:


    Public Sub Hide()
    Dim i As Integer
    For i = 178 To 219
    If Range("F" & i) = "" Then
    Rows(i).Hidden = True
    End If
    Next i
    End Sub

    but the following is much shorter:

    Public Sub Hide()
    Range("F178:F219").SpecialCells(xlCellTypeBlanks). EntireRow.Hidden = True
    End Sub

    I'd probably use command buttons from the Forms toolbar - one to hide, one to unhide. Not the most sophisticated interface, but easy to implement. A toggle button from the Control Toolbox would be relatively easy too, but it requires a bit more coding.

  2. #2
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    HIDE ROW IF ZERO (EXCEL 2003)

    I am tring to get a macro that looks at the contents in a range F178:F219
    each row in this range will either have a number in it or nothing

    if nothing then the row should hide

    the twist is that I am looking to unhide everything as well,

    I have two places in the worksheet where I need this so having a Autofilter and using no blanks doesnt work.

    I am thinking of trying the toggle button or a simple drop down which allows me to decide to hide rows if the contents in the corresponding cell to that row is blank or to showall
    depending on the option object
    option explicit

    Public Sub hide ()
    Dim i as integer

    i = 178 to 219

    While
    Range("F"&i) = ""
    row(i).Hidden = True
    Next i

    End Sub

    i am not sure which object ie drop down menu, toggle switch, check box would be best to initiate the macro??
    Any one have an idea, and some tips to help make my code snip work?

    Thanks

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HIDE ROW IF ZERO (EXCEL 2003)

    ok thanks

    I will look up the toggle button and see what i can do

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: HIDE ROW IF ZERO (EXCEL 2003)

    I would suggest:
    <pre>Public Sub Hide()
    On Error Resume Next
    Range("F178:F219").SpecialCells(xlCellTypeBlanks). EntireRow.Hidden = True
    End Sub</pre>


    to prevent the runtime error if there are no blanks

    Steve

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HIDE ROW IF ZERO (EXCEL 2003)

    OK here is my quick attemp with a bug or two

    Private Sub ToggleButton1_Click()


    If ToggleButton1.Value = True Then
    Range("F178:F219").SpecialCells(xlCellTypeBlanks). EntireRow.Hidden = True
    End If
    ToggleButton1.Caption = "Rows hidden!"
    Application.EnableEvents = False
    Else
    Range("F178:F219").SpecialCells(xlCellTypeBlanks). EntireRow.Hidden = False
    ToggleButton1.Caption = "Show All!"
    End Sub

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HIDE ROW IF ZERO (EXCEL 2003)

    Using steves Error handler

    Private Sub ToggleButton1_Click()

    On Error Resume Next
    If ToggleButton1.Value = True Then
    Range("F178:F219").SpecialCells(xlCellTypeBlanks). EntireRow.Hidden = True
    End If
    ToggleButton1.Caption = "Rows hidden!"
    Application.EnableEvents = False
    Else
    Range("F178:F219").SpecialCells(xlCellTypeBlanks). EntireRow.Hidden = False
    ToggleButton1.Caption = "Show All!"
    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HIDE ROW IF ZERO (EXCEL 2003)

    Why the Application.EnableEvents = False? You don't need that here.

    The line <code>End If</code> is in the wrong place - it should be immediately above the <code>End sub</code>.

  8. #8
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HIDE ROW IF ZERO (EXCEL 2003)

    Private Sub ToggleButton1_Click()

    On Error Resume Next
    If ToggleButton1.Value = True Then
    Range("F178:F219").SpecialCells(xlCellTypeBlanks). EntireRow.Hidden = True
    ToggleButton1.Caption = "Rows hidden!"
    Application.EnableEvents = False
    Else
    Range("F178:F219").SpecialCells(xlCellTypeBlanks). EntireRow.Hidden = False
    ToggleButton1.Caption = "Show All!"
    End If
    End Sub

    this works
    i fixed the if statment

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HIDE ROW IF ZERO (EXCEL 2003)

    well i saw it on a googlwe search thought it would make things more stable

    <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HIDE ROW IF ZERO (EXCEL 2003)

    You can set Application.EnableEvents to False temporarily in a worksheet event procedure such as Worksheet_Change or Worksheet_Calculate to prevent triggering other event procedures. Your ToggleButton1_Click procedure doesn't really need it, even if you have worksheet event procedures, since it doesn't change the value of any cells. Moreover, you should only turn off EnableEvents temporarily, i.e when you use Application.EnableEvents = False, you must always have Application.EnableEvents = True near the end of the procedure. Otherwise, worksheet events will stop working.

Posting Permissions

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