Click "-" or "+" to hide and unhide rows

Hi,
I received the help, it works if "+" or "-" in cloumn. I move the list lower and move one column it does not work any more.
Attached is the code.

Please advise.


Thanks

Hello Rocky2013,

Moving the List lower, will not effect the Code. However, if you move it Right, say one Column, you have to change the Column Number in the Code as well.i.e;

The Code below has been changed to work after moving the Data-set one Column to the Right.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column > 3 Then Exit Sub  ' Changed the original No.2 (Column B) to 3 (Column C) 
If Target.Count > 2 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

Regards

Hi,
Thanks for the reply. I change the number it does not hide the rows properly, it hides the previous row(s).
I need to the hide the rows below "-". Before "2" and "3" works the same way. I try to change
the numbers on ....... If Target.Column > 3, Target.Count > 2, CurrentRegion.Columns(1), SpecialCells (2),
SpecialCells (4); I could not make it work.

I do not understandd what those codes means and how those work except Target.Column > 2 = means the
column for "-" (I think).

Can you please expain what those means and how those affect the hidden rows.

Regards,

Hi,
It works, except in column B, there is not always has a sub-list. So in your attachment, I remove the "-"
in column B, from row 26 (Shoes) to row 32 (Others). What happen when I click "-" in cell B22, rows from 23
to 32 are hidden (except row 33 for some reason).

I Click "+" in B22, hidden rows 23-32 would not unhide. I like when I click "-" in B22 (Drinks), only row 23 - 25
are hidden. All other rows from row 26 to the end of remain shown, unless there is another "-" below row 22
somewhere because my document has mixing with "+ or -" and without any sign (single line of data).
So I assume the code would check "column B if the "+" or "-" are existed or not.

Please advise

Thanks of your help.


Regards,

Hi Rocky2013,

Sorry, I did not check that, and I was out for most of the day.

Please try the attached sample Workbook.

Regards

Hello Rocky2013,

Each Category must have the controlling "+" or "-" in the Column to the Left of each Category, otherwise the program regards it as the "Items" belonging to the last Category with the "+" or "-" in the cell on the left.

I have done that for you in the attached sample Workbook. If no Items are listed below any Category, of course, nothing will be hidden or displayed.

Hope that clears up the logic of the program for you.

Regards

Hi Winon,
I found there is a pop-up error meeage when I click the upper left corner to select all (left of column A and above row 1.
It indicates "Run-time error '6': Overflow". I click deburg, "If Target.Count > 1 Then" at the top is highlighted in yellow.

"How can I fix this, please advise.

Thanks

Hello Rocky2013,

Just cange that line to:

If Target.Rows.Count > 1 Then Exit Sub

If this has solved your problem, then please mark this Thread as Solved.

You may also Click on the Star to the far left, at the bottom of this Post, to Add Reputation.

Regards

Hi Winon,
It works now. Some day I have to find out what this code means to learn VBA.

I click star - add repution. Do not know what happen after I click.

Hello Rocky2013,

You are welcome.

Glad that it now works for you. Thank you also for having marked this thread as "Solved".

Regards

Hello Rocky2013,

I found another problem when selecting more than one Column. Maybe you would like the attached sample Workbook better?

Also when you click on "+" or "-", I have made it that the "Activecell" moves one Cell to the left, so that one does not have to first go to another Cell and then back again to hide or unhide any selections.

Just a thought.

Regards.