I am currently working on a project using Ms Excel. There are 50 Rows
on my excel sheet and i would like to hide some rows depending on the
inputs in other cells.
For e.g: In cells B31, the user selects one product from a drop down
list( A, B and C). Say the user selected products A, i want excel to
be able to unhidden everything that is related to A. i need code for
this..pls reply soon..its urgent
Percy/Pista/Johnny,
Your post is rather vague, so if you want a more concise answer,
please provide a more descriptive explanation along with any code you
have created; this will likely merit a better response from the group.
You can embed your code within the SelectionChange event to test
whether the drop down list has changed. (I'm assuming your drop down
is created from the data validation option). You can then run
ExecuteExcel4Macro to hide and unhide rows or columns (see sample code
below).
Best,
Matthew Herbert
Sub HideUnhide()
Dim rngExpand As Range
Dim strExecute As String
'create a range object to work with
Set rngExpand = Range("A2")
'--------------------------------------------------------------
'General Comments for hiding and unhiding groupings
'
'Show.Detail
' Arg1 = 1 (Row) or 2 (Column)
' Arg2 = Row or Column inside of or including the +/- outline
' Arg3 = True (Unhide) or False (Hide)
'
'--------------------------------------------------------------
'unhide a grouping
strExecute = "Show.Detail(1," & rngExpand.Row & ",True)"
ExecuteExcel4Macro strExecute
'hide a grouping
strExecute = "Show.Detail(1," & rngExpand.Row & ",False)"
ExecuteExcel4Macro strExecute
End Sub
