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

[ExcelJuke]

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

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

 

[Eric W]

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.

 

[ExcelJuke]

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.

 

[Eric W]

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.

 

[Yongle]

try this

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")

 

[Fluff]

@ExcelJuke
In future please do not post duplicate threads. I have merged both of your threads.