unhide rows when cell is clicked

[lauren_roberts08]

Hi everyone,

Is it possible to unhide a selection of rows when you click on a cell.
For example, if I had a cell that said "Fruit", and I clicked it, could
it unhide 5 rows below that contained specifc types of fruit (ie apple,
pear, orange etc).

I am in the very beginning stages of understanding excel so any help
would be greatly appreciated (but could it also be as straightforward
as possible!! thank you!!!)

Thanks!
lauren

[Barb Reinhardt]

You might want to look at Tools -> Autofilter. Do a search for autofilter
to get more info.

"lauren_roberts08" wrote:

> Hi everyone,
>
> Is it possible to unhide a selection of rows when you click on a cell.
> For example, if I had a cell that said "Fruit", and I clicked it, could
> it unhide 5 rows below that contained specifc types of fruit (ie apple,
> pear, orange etc).
>
> I am in the very beginning stages of understanding excel so any help
> would be greatly appreciated (but could it also be as straightforward
> as possible!! thank you!!!)
>
> Thanks!
> lauren
>
>

[Bearacade]

The other way of doing it involves VBA, I am not sure how comfortable you are with it.

You only asked for it to be unhide, but you should consider that the rows that are shown after a cell has been click "should" be hidden after someone leaves that cell.

Open up your Visual Basic Editor (Alt + F11) or go to Tools -> Macro -> Visual Basic Editor.

Go to Insert and select Module and put the following in it:

Sub unHideRows()

Dim Row1 As Integer
Dim Row2 As Integer

Row1 = ActiveCell.Row + 1
Row2 = Row1 + 4

Rows(Row1 & ":" & Row2).Select
Selection.EntireRow.Hidden = False

End Sub


Now within VB editor go to the sheet that your data is in (select it in the Project Explorer (the window on the left), and enter the following in it:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Resume Next
If Target.Address = "$A$1" Then
Call unHideRows
On Error GoTo 0
Exit Sub
End If

End Sub

Change $A$1 to the cell that will trigger the unhiding. if you have multiple cells, put this in:

If Target.Address = "$A$1" or Target.Adress ="$B$1" then

again, this is just to unhide it, hiding after you are done is another matter