use VBA to group rows in Excel

[Donbeall]

I have a fairly large spreadsheet to list customer details which is sorted by sales reps (about 40 reps). I want to be able to double click on a rep's name (in Column C) and have VBA automatically group the rows below it that have the same data in column C as what was in the cell that i double clicked in) with his name; then double click on the next rep and group, etc. The reps' names are always in column C.

For example, if i doubleclick in cell C4, it will "memorize" that row (4) and the contents of the cell C4 and look in row 5. If C5 contains the same value as C4, it will look in row 6. if cell C6 has the same value as C4, it will look in row 7. It will do this until it doesn't find a match - let's say C50 is different. it will then GROUP the rows C4 (starting row) to C48 (C49 is last match and it would have to go up one row to 48 so that row 49 would be visible when the group is collapsed).

Any advise/guidance would be appreciated.

 

[DanteAmor]

Put the following code in sheet's event

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim f As Range
  If Not Intersect(Target, Range("C:C")) Is Nothing Then
    Cancel = True
    Set f = Range(Cells(Target.Row, Target.Column), Cells(Rows.Count, Target.Column)).Find(Target, , xlValues, xlWhole, , xlPrevious)
    If Not f Is Nothing Then
      Dim fila
      fila = f.Row
      If f.Row > Target.Row Then
        Range(Target, Cells(f.Row - 1, Target.Column)).Rows.Group
      End If
    End If
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[Donbeall]

Thank you very much Dante! Works great! What if i wanted to only do one group at a time, instead of grouping the whole spreadsheet at the same time - to perform the action only on the name i double click on? I wish i could attach a small sample worksheet but for some reason i am unable to.

 

[Donbeall]

Nevermind Dante, i see now that's exactly what it does. PERFECT!! Thanks again very much.

 

[DanteAmor]

Nevermind Dante, i see now that's exactly what it does. PERFECT!! Thanks again very much.

I'm glad to help you. Thanks for the feedback.