use VBA to group rows in Excel

Donbeall

New Member
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

Well-known Member
Put the following code in sheet's event


Code:
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

New Member
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.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top