Hello,
I like to have a VBA to expand and collapse rows of cells. I do not like data>group which
add a wide column on the left side with "+" or "-".
Thanks
Hello,
I like to have a VBA to expand and collapse rows of cells. I do not like data>group which
add a wide column on the left side with "+" or "-".
Thanks
Last edited by Rocky2013; 08-24-2013 at 03:19 PM.
I think something like this:
![]()
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim count If Target = "+" Then count = Target.Row + 1 Do Until Cells(count, Target.Column) = "+" Or Cells(count, Target.Column) = "-" count = count + 1 Loop Range(Cells(Target.Row, Target.Column), Cells(count, Target.Column)).EntireRow.Hidden = False ElseIf Target = "-" Then count = Target.Row + 1 Do Until Cells(count, Target.Column) = "+" Or Cells(count, Target.Column) = "-" count = count + 1 Loop Range(Cells(Target.Row + 1, Target.Column), Cells(count - 1, Target.Column)).EntireRow.Hidden = True End If End Sub
Hello,
It seems there is some issue with the codes.
1. I copied the code to sheet1,
2. Click "-" in A6, rows 7 to 11 collapes, but the "-" did not change to "+".
3. Click "-" in A6 to expand the rows, nothing happen.
4. Click A12, it works the same exact ways,
5. Click A17 to collapes the rows, it did not work.
Can you plesae or anyone take a look.
Thanks
![]()
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim count If Target = "+" Then count = Target.Row + 1 Do Until Cells(count, Target.Column) = "+" Or Cells(count, Target.Column) = "-" count = count + 1 Loop Range(Cells(Target.Row, Target.Column), Cells(count, Target.Column)).EntireRow.Hidden = False Target = "-" ElseIf Target = "-" Then count = Target.Row + 1 Do Until Cells(count, Target.Column) = "+" Or Cells(count, Target.Column) = "-" count = count + 1 Loop Range(Cells(Target.Row + 1, Target.Column), Cells(count - 1, Target.Column)).EntireRow.Hidden = True Target = "+" End If End Sub
Hi,
Thanks for trying again. I click A6 and A12, the rows collapsed, but I click again, the hidden rows did
not show up immediately. If I wait 30 seconds, the rows may unhid. Some case the rows did not unhide.
The third one A16 does not works either ways. There is pop-up message "run-time error '1004': Application
-defined or object-defined error. ". Is there way to change the sign, after the cell is bewteen toggleed?
Thanks
Hi Rocky,
try it
![]()
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column > 1 Then Exit Sub If Target.count > 1 Then Exit Sub If (Target <> "-") * (Target <> "+") Then Exit Sub Dim i As Long: Application.EnableEvents = False With Target.CurrentRegion.Columns(1) With .SpecialCells(2) For i = 1 To .Areas.count If .Areas.Item(i).Address = Target.Address Then Exit For Next End With .SpecialCells(4).Areas.Item(i).EntireRow.Hidden = Target = "-" End With Application.EnableEvents = True Target.Value = IIf(Target = "+", "-", "+") End Sub
Hi nilem,
The VBA works seamless, it even changes the sign. Thanks again.
Hi yodlugar,
Even I am not using yours. I am very appreicate your time and effort. I am sure I will back with other helps while
I am slowing learn VBA starting from first grade. Thanks again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks