Drilling down in excel

[baggarwal]

Hi All:

In the spreadsheet attached, what I want to do is setup a drilldown so that if someone clicks a cell with a value in it, it will show everything that makes up that number.

Book1
	A	B	C	D
3	Relay	2201
4	PR1	500
5	HW	100
6	SW	200
7	Consulting	200
8	PR2	678
9	TR1	567
10	TR2	456
Sheet1

So in the example attached. I want to setup the spreadsheet so that if someone double clicks cell B3, which is $2,201 then everything that makes it up...i.e cells B4, B8, B9 and B10 are shown otherwise hidden. If someone wants to double click B4 then I want cell cells A5 to B7 to show up.

Can someone please help work through this.

Thanks,

BA

 

[Tom Urtis]

You asked for 2 scenarios so here they are, one for B3 and one for B4.

Be careful with this, it literally does what you want, which is to only show a small visible range, but in so doing it hides about 16,777,210 other cells, albeit clumped in column abd row ranges, but still, depending on your system resources, can have an effect on memory. Excel is a 2-dimensional program (height in rows and width in columns), so the sequence of the code is to unhide everything, then selectively re-hide what you don't want to see.

It would be better to limit the scroll area to where you want your user to go when they have drilled down, instead of all this hiding. or, maybe dialog boxes or user forms would be more efficient to show the drilled down information. All options are your decision...here's what you literally requested:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
'To show only A102:B105
Case "$B$3"
If Target = "" Then
Cancel = True
Else
Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False
Range("C:IV").EntireColumn.Hidden = True
Range("1:101,106:65536").EntireRow.Hidden = True
Application.ScreenUpdating = True
Cancel = True
End If
'To show only A102:A105 and C102:C105
Case "$B$4"
Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False
Range("B:B,D:IV").EntireColumn.Hidden = True
Range("1:101,106:65536").EntireRow.Hidden = True
Application.ScreenUpdating = True
Cancel = True
Case Else
Exit Sub
End Select
End Sub