Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Feb 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Collapsing rows with certain criteria macro (Office 2003)

    I am looking for a macro that I can attach to a command button that when clicked collapses all rows that have a value of "0" in the "Quantity" column. This makes all relevant rows show and hides the unecessary rows. I am including the template. Any help is appreciated.
    Attached Files Attached Files

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

    Re: Collapsing rows with certain criteria macro (Office 2003)

    Try this:

    Sub HideZeros()
    Dim r As Long
    Application.ScreenUpdating = False
    For r = 13 To Range("H65536").End(xlUp).Row
    If Application.WorksheetFunction.IsNumber(Range("H" & r)) Then
    If Range("H" & r) = 0 Then
    Range("H" & r).EntireRow.Hidden = True
    End If
    End If
    Next r
    Application.ScreenUpdating = True
    End Sub

  3. #3
    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: Collapsing rows with certain criteria macro (Office 2003)

    A simple way without a macro to do this would be to:
    select H12:H212
    Data-filter- autofilter
    Select the item in Quantity
    Custom
    "Does not Equal"
    "0"
    [OK]

    This can be filtered or expanded as desired without any code...

    Steve

  4. #4
    New Lounger
    Join Date
    Feb 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collapsing rows with certain criteria macro (Office 2003)

    Do I click on a drawn command button in design mode and then insert this code into the VB editor? Also does the letter "r" mean range? I see that you have the value r=13 so you had this spreadsheet specifically in mind when you created that macro; but is there anything I'll need to do to this code (out beyond placing it in the right spot) to get it to work for me?

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

    Re: Collapsing rows with certain criteria macro (Office 2003)

    One way to create the macro is the way you describe; you'd omit the Sub ... and End Sub lines because they are automatically created for you when you double click the button in design mode.
    Another way is to select Tools | Macro | Macros..., enter HideZeros in the Macro Name box, then click Create.

    The letter r is the name of a variable, it is essentially meaningless but I chose it because it acts as the row number of the cell being checked.
    I assumed that you didn't want to hide any of the rows 1 through 12, even if there is a 0 in column H, that's why the loop starts at r = 13.

  6. #6
    New Lounger
    Join Date
    Feb 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collapsing rows with certain criteria macro (Office 2003)

    I understand everything about this collapse rows macro except for one thing: I dont get the "H65536" part. Does that mean that you are making th range go from h13 to h65536? I am trying to make macros that open and collapse certain sections of that spreadsheet. I think if I totally understood your macro I could do something better than this extrememly lame way that I tried to program the other buttons. The sheet still comes in under 100k so I'll send it to let you know what I am talking about.
    Thanks Hans
    You Rock!!!

  7. #7
    New Lounger
    Join Date
    Feb 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collapsing rows with certain criteria macro (Office 2003)

    Oops sorry I forgot the spreadsheet.
    Attached Files Attached Files

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

    Re: Collapsing rows with certain criteria macro (Office 2003)

    Range("H65536") is the last (bottom) cell in column H, at least in Excel 2000 - 2003; in Excel 2007, the number of rows is *much* larger.

    Range("H65536").End(xlUp) moves up from the bottommost cell in column H until a non-blank cell is encountered, i.e. the last used cell.

    Range("H65536").End(xlUp).Row is the row number of the last used cell in column H.

  9. #9
    New Lounger
    Join Date
    Feb 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collapsing rows with certain criteria macro (Office 2003)

    I have tried this several different ways but I can't get it to work. VB editor acts like all is fine until I run it and then de###### starts. By the way how do I keep the buttons in their place or from dissappearing altogether when I collapse the rows.
    Thanks
    Attached Files Attached Files

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

    Re: Collapsing rows with certain criteria macro (Office 2003)

    Edited by HansV to correct error

    Here are some suggestions:

    - To avoid screen flicker while hiding rows, turn off screen updating temporarily.
    - You can unhide cells in a range without selecting it.
    - The code for hiding/unhiding zeros in each range is the same except for the name of the range, so you can create procedures that take the range name as argument; this takes out part of the repetition.

    Example:

    ' Common code

    Sub HideZeros(RangeName As String)
    Dim CheckCell As Range
    Application.ScreenUpdating = False
    For Each CheckCell In Range(RangeName)
    If CheckCell.Value = 0 Then
    CheckCell.EntireRow.Hidden = True
    End If
    Next CheckCell
    Application.ScreenUpdating = True
    End Sub

    Sub UnhideZeros(RangeName As String)
    Range(RangeName).EntireRow.Hidden = False
    End Sub

    ' Call this from the command buttons:

    Private Sub CommandButton5_Click()
    HideZeros "MaterialsRange"
    End Sub

    Private Sub CommandButton6_Click()
    UnhideZeros "MaterialsRange"
    End Sub

    Private Sub CommandButton7_Click()
    HideZeros "SubLaborRange"
    End Sub

    Private Sub CommandButton8_Click()
    UnhideZeros "SubLaborRange"
    End Sub

    ' etc.

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

    Re: Collapsing rows with certain criteria macro (Office 2003)

    1) I apologize, my code contained a stupid error - both in HideZeros and in UnhideZeros, I should have used Range(RangeName) instead of Range("RangeName").

    2) In Excel, activate design mode (1st button on the Control Toolbox).
    Select the command buttons (for example click the first one, then Shift+Click subsequent ones).
    Select Format | Control...
    Activate the Properties tab.
    Click "Don't move or size with cells".
    Click OK.

  12. #12
    New Lounger
    Join Date
    Feb 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collapsing rows with certain criteria macro (Office 2003)

    The code works beautifully but I can only get the first two sets of buttons to hold their position. I formatted the last four sets but they won't stay still. Heres the file again.
    Thanks Much
    Clint
    Attached Files Attached Files

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

    Re: Collapsing rows with certain criteria macro (Office 2003)

    I think you'll have to move the buttons to rows that don't get collapsed, for example above the blocks of cells instead of to the left. Experiment with the settings in the Properties tab of Format Control.

  14. #14
    New Lounger
    Join Date
    Feb 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collapsing rows with certain criteria macro (Office 2003)

    I originally made the buttons from the forms toolbar instead of the Toolbox toolbar so I thought maybe it would make a difference if I created the buttons from the toolbox but it didn't change anything. I have thoroughly checked the properties and the format control and they are uniform for all of the buttons yet the top two sets (All and Materials) of buttons work fine and the others do not. Do you think the file is corrupted somehow? I ran "Detect and Repair" because that had previously gotten some of my Word macros to run correctly but nothing. It sort of seem like the buttons would go upwards if they were going with cells but they go way downwards. It just doesn't make sense.

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

    Re: Collapsing rows with certain criteria macro (Office 2003)

    Try the attached version. I have moved the buttons to above the tables they refer to and have set their behavior to "Move but don't size with cells".

    Notes:
    - I didn't bother to align the buttons carefully.
    - You may want to move some labels.
    - Check your spelling (carefull, manger)
    Attached Files Attached Files

Posting Permissions

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