use VBA to group rows in Excel

[BeallDon]

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 and have VBA automatically group the rows with his name; then double click on the next rep and group, etc. The reps' names are always in column C.

Any advise/guidance would be appreciated.

 

[SkipVought]

Hi,

Why wouldn't a Filter accomplish this end?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[BeallDon]

i do use filters but i want to group them by rep names so the when i filter a specific territory, i can see all reps on one screen (there may be 6-10 reps in each territory and up to 100 customers per rep). Not looking to group by territory, just reps because the spreadsheet isn't sorted by territory. in the past, the previous person would group all the reps accounts manually but i know VBA can make that process much quicker.

 

[SkipVought]

Please post a sample of your table that illustrates why you cannot filter by [Sales Rep].

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[BeallDon]

Skip: I never said i CANNOT filter by sales rep. i said i DO use filters. But i WANT to be able to double-click on the rep's name in column C and have VBA automatically GROUP all the rows immediately below with the same rep name (whether it be 40 rows or 100 rows). i just don't want to manually GROUP the rows for about 40 reps every few days when the data for the spreadsheet is updated.

 

[SkipVought]

Please post a representative sample of data that illustrates exactly what you are referring to.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[BeallDon]

Skip, see attached.

I want to be able to double-click cell C2 and automatically GROUP rows 2-39 (actually 2-38 because row 39 would be visible) because they all have the same data in Column C.

 

[SkipVought]

Have you tried using a PivotTable?

I modified your data in order to display multiple Sales Reps
[pre]
TERRITORY REP REP # CUST # TYPE
CENTRAL KENN HUYNH KAR MM 765 107807 KEY ACCOUNTS MM
127409 KEY ACCOUNTS MM
127414 KEY ACCOUNTS MM
127422 KEY ACCOUNTS MM
SkipVought 765 127170 KEY ACCOUNTS MM
127412 KEY ACCOUNTS MM
127417 KEY ACCOUNTS MM
127421 KEY ACCOUNTS MM
127423 KEY ACCOUNTS MM
127426 KEY ACCOUNTS MM
[/pre]

Here's the drill.
Decide what Excel feature you want to happen when you double-click.
Then turn on your macro recorder and record performing the steps to actualize that feature.
Turn off the recorder.
COPY 'n' Paste the recorded coded here for help customizing your code for the double-click.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[BeallDon]

Skip, I already know what i want to accomplish. i am simply looking for a bit of guidance to perform a repetitive task. i don't need to modify the spreadsheet and i'm not going to start messing with pivot tables, etc. This is a spreadsheet provided to me by the company i work for and i'm not in a position to start dictating how they structure the data. with respect, if you have can't answer my question in simple terms, then maybe just let someone else answer it directly. thank you.

 

[SkipVought]

What do YOU mean by GROUP? There is a GROUP feature in Data > Outline > GROUP.

What you need to show is what is in YOUR mind when you say Group by Rep in that table. So please post what you want the result to look like.

BTW, "i don't need to modify the spreadsheet" is exactly opposite of what you seem to be asking for, unless your definition and my definition of Group are different. Just for clarification, a PivotTable changes nothing in the source data. So the source data is totally untouched in such an approach. It is a display report of untouched source data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[BeallDon]

This is EXACTLY what i'm looking to do:

See attached. Do you see the "+" or "-" sign on the left of column A? That's what i mean by grouping. I didn't know there was another grouping function in Excel. I want to GROUP all ROWS that contain the same data in column C when i double-click on a specific cell in column C. For example, if i double-click (with my mouse), cell C2, it will look at the same column in the row right below (in this case, c3, and if it matches c2, it will go to the next row and look in the same column, C4 and if it matches C2, it will look in the next row, same column, C5 and if it matches C2, it will look in the next row, and etc until it doesn't find a match of C2, then it will go up 2 rows from the non-matched row (in this example spreadsheet. row 40 is blank - not a match - so it would "remember" row 38) and GROUP the starting row where i double clicked on (in this case, row 2) and select all rows in between row 2 and row 38 (inclusive of 2 and 38) and GROUP them so i have the "+" sign to expand them when appropriate - i say row 38 because that way row 39 would be exposed when the group is collapsed.

What i mean by not modifying the spreadsheet is i don't want to change the layout which is what you suggested i do. there ARE multiple sales reps (approximately 40 of them as i previously specified) which is why i want to make this repetitive procedure faster. Each time the data in the spreadsheet is updated (which is every 2 or 3 days), i have to go through the task of manually selecting the rows for each sales rep and grouping them.

All i'm looking for is a few lines (whether it be 15 lines or 50 lines) of VBA code. The "macro recorder" doesn't help at all. This is all i get with the macro recorder:

Sub Group()
'
' Group Macro
'

'
    Rows("2:38").Select
    Selection.Rows.Group
End Sub

 

[SkipVought]

You've only given us an example of ONE Rep.

How about a Group example using more than one Rep? Need to see that too.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[BeallDon]

Skip, If you don't understand what i'm after, or if you have no idea how to accomplish it, then kindly, don't respond. I feel I have clearly outlined my goal. The code would be the same no matter what the name of the rep is - if there were 2 reps, then there would be 2 GROUPS instead of just one or 20 groups if there are 20 reps or 156,789 groups if there are 156,789 reps. Please disregard my thread from this point on, thank you.

 

[SkipVought]

Have your ever considered the Data > Outline > Subtotal feature?

BTW, does your current manual process involve inserting a row between Reps?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Well, I made some assumptions.

Double click on any cell in the column you want to group on. The procedure will group all like adjacent values in the column, assuming that the data in the table begins in row 2 and that there are no empty rows in the table.

Paste this code in the Sheet Code Page. Right-Click the Sheet Tab and select View Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim r As Range, sPrev As String, lr1 As Long, lr2 As Long
    
    With Target
        Cancel = True
        
        sPrev = Cells(2, .Column).Value
        lr1 = 2
        For Each r In Range(Cells(2, .Column), Cells(.End(xlDown).Row, .Column))
            
            If sPrev <> r.Value Then
                lr2 = r.Row - 2
                
                MakeGroup lr1, lr2
                
                sPrev = r.Value
                lr1 = r.Row
            End If
        Next
        lr2 = Cells(.End(xlDown).Row, .Column).Row - 1
    End With
    
    MakeGroup lr1, lr2
    
End Sub

Sub MakeGroup(lRow1 As Long, lRow2 As Long)
'
' Group Macro
'

'
    Rows(lRow1 & ":" & lRow2).Rows.Group
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Why, I posted a solution that works on your table data modified to have multiple Reps so I could run a successful test?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[BeallDon]

Skip,

Do you see how simple this was? You were making this sound so complicated.

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

 

[SkipVought]

Oh, I didn't realize that you would rather double-click 156,789 times on 156,789 specific cells rather than one time, anywhere in the column of interest.

I'd start to get tired and annoyed after, I dunno, eight times. I'druther do something fun or productive.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein