Macro to hide rows below

[Scott Marcus]

I have a spreadsheet in which column A has customers' names and other columns
have other info. Some customers have only 1 row worth of info, while others
have several rows (all directly below each other). I would like to be able to
double click the customer's name and if there are multiple rows for this
customer (meaning there is no customer name directly below, but rather a few
rows down), it will hide the rows below until the next customer's name. And
another double click will unhide the rows. For example, A1 will have "Smith".
A4 will have "Johnson". A5 will have "Brown". A9 will have "Davis". If I
double click A1 it will hide rows 2-3. If I double click A5, it will hide
rows 6-8.

If anyone can help me I would be very grateful.
Thanks, Scott

 

[Rick Rothstein]

The Worksheet_BeforeDoubleClick event code below should do what you asked.
To install it, right click the tab at the bottom of the worksheet you want
this functionality on, select View Code from the popup menu that appears and
copy/paste the code below into the code window that appeared...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim X As Long
Dim LastRow As Long
Dim MaxRowInUse As Long
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Cancel = True
If Len(Target.Value) > 0 Then
For X = 1 To ActiveSheet.UsedRange.Columns.Count
LastRow = WorksheetFunction.Max(Cells(Rows.Count, X). _
End(xlUp).Row, ActiveSheet.UsedRange.Rows.Count)
If LastRow > MaxRowInUse Then MaxRowInUse = LastRow
Next
For X = Target.Row + 1 To MaxRowInUse
If Len(Cells(X, "A").Value) > 0 Or X = MaxRowInUse Then
If X > Target.Row + 1 Or X = MaxRowInUse Then
Cells(Target.Row + 1, "A").Resize(X - Target.Row - 1 - _
(X = MaxRowInUse)).EntireRow.Hidden = _
Not Cells(Target.Row + 1, "A").EntireRow.Hidden
End If
Exit For
End If
Next
End If
End If
End Sub