Expand/Collapse Rows with Data ONLY

[Bkisley]

Let's say column C is where I am listing our all my components/parts and column H is where I have that component's final cost. One row being one component. For any components that are manufactured by us I then insert a row for each manufacturing process. Some components have 2 processes some have 10 etc. I want to be able to toggle between top level aka component name and final cost then be able to toggle to see all the detail. My initial thought was to add a row between each row and create groupings. Then by clicking 1 I would get to the top level I am after - component and final cost or by clicking 2 I could see all the detail. The problem with that is by clicking 2 It expands all the empty rows I have. My goal is to find a way, assuming by using VBA, to expand/collapse only the rows that have data in them. Hope this makes sense and thank you in advance!

 

[VBE313]

Assuming you have a column to show the difference of a "TOP LEVEL" component vs. an operation sequence like 1 = "TOP LEVEL" you can do something with a toggle button. I put the levels in column A. If the cell = 1, then it will show all the "TOP LEVEL" components. I have the toggle to switch to breakdown and it will show all operations that aren't blank.

Insert a toggle button in the active worksheet and make sure the toggle button is named ToggleButton1. Double click the button and paste this code in here.

Private Sub ToggleButton1_Click()
Dim cl As Object, lastRow As Long, rng As String
With ActiveSheet
    lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    rng = "A1:A" & lastRow
    For Each cl In .Range(rng)
        If ToggleButton1.Value = True Then
            ToggleButton1.Caption = "Show Top Levels"
            Select Case cl.Value
                Case ""
                    cl.EntireRow.Hidden = True
                Case 1
                    cl.EntireRow.Hidden = False
                Case Else
                    cl.EntireRow.Hidden = False
            End Select
        Else
            ToggleButton1.Caption = "Show Breakdown"
            Select Case cl.Value
                Case ""
                    cl.EntireRow.Hidden = True
                Case 1
                    cl.EntireRow.Hidden = False
                Case Else
                    cl.EntireRow.Hidden = True
            End Select
        End If
    Next cl
End With
End Sub

 

[Bkisley]

Hello and thank you for your response. I finally got to work on this today and am struggling with 2 things yet. When I click it to go from all detail to itemized/top level it works great. However when I click it to go from itemized/top level to the breakdown - it doesn't work. It doesn't unhide anything. The second issue I am running into is that 215 end row needs to be dynamic. I will be adding in rows which will move that 215 down. How do I make this dynamic so that when I add in rows somewhere between the initial 12:215 that 215 will move with it?

Once again thank you very much for the initial help!

 

[Bkisley]

Scratch that...down to one issue. I change the following....
ToggleButton1.Caption = "Show Breakdown"
Select Case cl.Value
Case ""
cl.EntireRow.Hidden = True --> False
Case 1
cl.EntireRow.Hidden = False
Case Else
cl.EntireRow.Hidden = True

Now I just need help with the dynamic range portion

 

[Bkisley]

argh so sorry! the whole 215 thing mentioned above is a different macro issue I was having that I accidentally replied to on this thread. I got what I asked you of after the change mentioned above. Thanks again and consider this closed