Basic VBA question - hiding rows

[Babymech]

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.

 

[Bob Phillips]

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.

 

[Babymech]

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?

 

[Pete_UK]

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

 

[Babymech]

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?

 

[Bob Phillips]

Type this in the immediate window in the VBIDE

Application.EnableEvents = True