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.

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.

Thanks for the quick reply - unfortunately I can't get that to work... When I
insert the code it immediately marks the line cell.entirerow.hidden = true
as red, and when I try to run it, I get a syntax error. VBA tells med it
expects an expression there... Any ideas?

"Bob Phillips" wrote:

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

Note that these lines:

cell.EntireRow.Hidden = Not
cell.EntireRow.Hidden

should be all one line - it has wrapped in the posting, and should be
like this:

cell.EntireRow.Hidden = Not _
cell.EntireRow.Hidden

or like this (without indents):

cell.EntireRow.Hidden = Not cell.EntireRow.Hidden

Hope this helps.

Pete

On Jan 15, 10:21*am, Babymech
wrote:
Thanks for the quick reply - unfortunately I can't get that to work... When I
insert the code it immediately marks the line *cell.entirerow.hidden = true
as red, and when I try to run it, I get a syntax error. VBA tells med it
expects an expression there... Any ideas?



"Bob Phillips" wrote:
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- Hide quoted text -

- Show quoted text -

Hmm... That does help, part of the way... after getting the formatting right
there's no longer a compile error, but now I don't know where the error is.
I've checked that the code is in the right place, but when I make a selection
change in the appropriate sheet, nothing happens. I know that macros are
enabled, since if I leave the incorrect formatting in there it shows an error
when I change selection in that sheet, but when the correct code is in place,
nothing at all happens. Any ideas?

"Pete_UK" wrote:

Note that these lines:

cell.EntireRow.Hidden = Not
cell.EntireRow.Hidden

should be all one line - it has wrapped in the posting, and should be
like this:

cell.EntireRow.Hidden = Not _
cell.EntireRow.Hidden

or like this (without indents):

cell.EntireRow.Hidden = Not cell.EntireRow.Hidden

Hope this helps.

Pete

On Jan 15, 10:21 am, Babymech
wrote:
Thanks for the quick reply - unfortunately I can't get that to work... When I
insert the code it immediately marks the line cell.entirerow.hidden = true
as red, and when I try to run it, I get a syntax error. VBA tells med it
expects an expression there... Any ideas?



"Bob Phillips" wrote:
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- Hide quoted text -

- Show quoted text -

Hmm... that does help a little, but now I don't know where the error is -
when I had the wrong formatting in there, and I went to the appropriate sheet
and made selection-change, it showed me the error. When the correct code is
in place, nothing happens - no error message, but no effect on the rows,
either. Any ideas?

"Pete_UK" wrote:

Note that these lines:

cell.EntireRow.Hidden = Not
cell.EntireRow.Hidden

should be all one line - it has wrapped in the posting, and should be
like this:

cell.EntireRow.Hidden = Not _
cell.EntireRow.Hidden

or like this (without indents):

cell.EntireRow.Hidden = Not cell.EntireRow.Hidden

Hope this helps.

Pete

On Jan 15, 10:21 am, Babymech
wrote:
Thanks for the quick reply - unfortunately I can't get that to work... When I
insert the code it immediately marks the line cell.entirerow.hidden = true
as red, and when I try to run it, I get a syntax error. VBA tells med it
expects an expression there... Any ideas?



"Bob Phillips" wrote:
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- Hide quoted text -

- Show quoted text -

Type this in the immediate window in the VBIDE

Application.EnableEvents = True

--
__________________________________
HTH

Bob

"Babymech" wrote in message
...

Hmm... that does help a little, but now I don't know where the error is -
when I had the wrong formatting in there, and I went to the appropriate
sheet
and made selection-change, it showed me the error. When the correct code
is
in place, nothing happens - no error message, but no effect on the rows,
either. Any ideas?

"Pete_UK" wrote:

Note that these lines:

cell.EntireRow.Hidden = Not
cell.EntireRow.Hidden

should be all one line - it has wrapped in the posting, and should be
like this:

cell.EntireRow.Hidden = Not _
cell.EntireRow.Hidden

or like this (without indents):

cell.EntireRow.Hidden = Not cell.EntireRow.Hidden

Hope this helps.

Pete

On Jan 15, 10:21 am, Babymech
wrote:
Thanks for the quick reply - unfortunately I can't get that to work...
When I
insert the code it immediately marks the line cell.entirerow.hidden =
true
as red, and when I try to run it, I get a syntax error. VBA tells med
it
expects an expression there... Any ideas?



"Bob Phillips" wrote:
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- Hide quoted text -

- Show quoted text -