Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit
Dim cell As Range

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If .Row 3 Then

If .Value = Me.Range("A3").Value Then

For Each cell In Me.Range("A4").Resize(.Row - 3)

If cell.Value < Me.Range("A3").Value Then

cell.EntireRow.Hidden = Not
cell.EntireRow.Hidden
End If
Next cell
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Babymech" wrote in message
...
I'm trying to learn a little bit of VBA, and I think what I'm trying to do
right now is pretty simple - it's just that it's a lot of things (for
someone
with my lack of knowledge) to put together.

What I want to do is allow users to click on a cell and have excel hide a
large number of rows based on which cell was clicked. Here is a general
process description of how I think of it, in terms of excel:

1) In Column A, Row 3, I have entered the word "Length". In the columns
that
follow, I have a number of different value representing lengths. In column
A,
row 18, the word Length appears again, followed in subsequent columns by
several values.

2) When the user clicks on any cell in Column A containing the word
"Length," this macro should determine how many cells contain text in
column A
(using end.xlup, I guess?), and define a range from row 3 to row whatever
the
last row with a text in column A is.

3) Then, once this is defined, excel should hide all rows in the range of
Row3:RowX for which the word in Column A is not "Length" (ie hide all
other
rows).

4) Finally, when the user clicks on the word "Length" again, the macro
will
unhide all rows.

Does this seem reasonable and rational? I know one might do something sort
of similar by just using tables, but that would screw with other things
I'm
trying to do. Does my description make sense, and are there smarter ways
of
doing what I want to do in VBA?

Thanks for any and all help.