HELP !!VBA to Collapse and Expand Rows Based on cell value

ExcelJuke

New Member
Joined
Aug 14, 2019
Messages
2
Calling all excel wizards- I am new to macros but I need to get this right for a project at work.! I am trying to create a macro that expands and collapsesrows based on a cell value. The macrothat I wrote below works like a charm EXCEPT I have to click out of the targetcell and click it again to expand or collapse.



Is there a simple way to fix this? IÂ’m willing to change thewhole macro if need be.






HereÂ’s the information:



My Target Cell is A5. When I click A5 I need the macro tosearch column J and hide every row that does not have an “87” in column J.


In column “J” I’ve put an “87” for the rows that I do notwant to hide and a “77” for the rows that I want hidden


The cell value for A5 shows as “-” when the rows areexpanded and “+“when the rows are collapsed.





My current macro says that if the cell value for A5 is “+”then run macro “Expand_LargeCap” whichhide every row that has “77” in column J.

Again these VBAS work fine together EXCEPT When I click A5 and it expands the rows I have to click anothercell and thennn click A5 again to collapse. How can I do it with just clickingA5 once to expand and clicking A5 againto collapse without having to click an empty cell in between





See below
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A5:A5")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    
    If Target.Value = "-" Then
   Collapse_LargeCap
    
    ElseIf Target.Value = "+" Then
    Expand_LargeCap
    End
    End If
    
End If







Sub Expand_LargeCap()
Dim cell As Range
    For Each cell In Range("j5:j11")
        If UCase(cell.Value) > 0 Then
        cell.EntireRow.Hidden = False
        End If
        
    Next
    For Each cell In Range("A5:A5")
    cell.Value = ("-")
    Next cell

End Sub
Sub Collapse_LargeCap()
Dim cell As Range
    For Each cell In Range("j5:j11")
        If UCase(cell.Value) = 77 And cell.EntireRow.Hidden = False Then
        cell.EntireRow.Hidden = True
       End If
       
    
    Next
    For Each cell In Range("A5:A5")
    cell.Value = ("+")
    Next cell
End Sub
 
Last edited by a moderator:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Re: VBA to expand and collapse rows in excel

Instead of a SelectionChange, why not just use a Change event? Then whenever you change A5 from + to - it'll rerun, or vice versa.

Or you could change it to a BeforeDoubleClick event. You'd probably want to set the Cancel to True. Then you can double click on it as much as you want without moving the mouse.
 
Upvote 0
Re: VBA to expand and collapse rows in excel

Thank you for your reply!! I can't use change because then the rows would just keep continuously expanding and collapsing. I want to be able to click on A5 and run the macro to expand so I can see the data in rows beneath and then click on A5 again run the macro to collapse without having to click an empty cell in between.
 
Upvote 0
Re: VBA to expand and collapse rows in excel

No, they would not continually expand or collapse. There's a line in the macro that only looks at A5. You can change anything else in the sheet and nothing would happen.

If you want to run it with a click, you can try the DoubleClick event like I mentioned. It takes 2 clicks, so it's slightly more onerous. But you can't expect the SelectionChange event to trigger when the selection does not change.

Another option is to add a button to the ribbon. Click File > Options > Customize Ribbon > Choose commands from: > Macros > select your macro > select a tab from the panel on the right > Add. If you add one button for Expand, and one for Collapse, you don't need to populate A5 at all.
 
Upvote 0
try this

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, [COLOR=#006400][I]Range("A5")[/I][/COLOR]) Is Nothing Then
        If [COLOR=#006400][I]Target.Cells.CountLarge[/I] [/COLOR]> 1 Then Exit Sub

        If Target.Value = "-" Then
            Collapse_LargeCap
        ElseIf Target.Value = "+" Then
            Expand_LargeCap
            End
        End If
        [COLOR=#ff0000]Range("B5").Select[/COLOR]
    End If
    
End Sub

Note also suggestions in green
.CountLarge can handle bigger numbers than .Count
Range("A5:A5") is the same as Range("A5")
 
Last edited:
Upvote 0
@ExcelJuke
In future please do not post duplicate threads. I have merged both of your threads.
 
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top