Expand/Collapse Rows with Data ONLY

Bkisley

Board Regular
Joined
Jan 5, 2017
Messages
73
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!
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
664
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

Snag_fd45631.png

Snag_fd44ece.png
 

Bkisley

Board Regular
Joined
Jan 5, 2017
Messages
73
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

Board Regular
Joined
Jan 5, 2017
Messages
73
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

Board Regular
Joined
Jan 5, 2017
Messages
73
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,610
Messages
5,548,983
Members
410,887
Latest member
sjohn627
Top