PDA

View Full Version : Solved: grouping based on cell value



khalid79m
03-12-2008, 04:51 PM
i need help to write some code. I need the code to START at cell E5 and check down through column E until it hits a cell value which is different to E5 then group the rows upto that new value but not including that new value in the group. I want it then to miss the next row and repeat the process. Eg E5 to E60 were all the same value eg "john smiti" and E61 was "Total" and E62 to E65 were all "micheck" and E66 was "Total" the script would group rows E5 to E60 then ignore E61 and start from E62 to E65 and group the rows then ignore E66 and do this until it hits a blank cell. Just bearing in mind the last cell will be "Total"
Any help im desperate, my boss wants it.. Don't want to let him down

anandbohra
03-13-2008, 12:11 AM
I suppose the subtotal will best fit in your case
u can set at every change in so & so do this
& at the bottom it will gives u summary for that.


but before doing sub total sort the data for the column u want subtotal so all values comes at one place.

khalid79m
03-13-2008, 01:27 AM
im not that knowledgable on vba. I don't understand how your answer would help?

Bob Phillips
03-13-2008, 01:51 AM
He is saying no need for VBA, use DAta>Subtotal.

But are you saying there is already a row saying Total?

Bob Phillips
03-13-2008, 01:58 AM
Assuming it is, use



Public Sub ProcessData()
Dim LastRow As Long
Dim StartAt As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
StartAt = 5
For i = 6 To LastRow

If LCase(.Cells(i, "E").Value) Like "*total*" Then

.Rows(StartAt).Resize(i - StartAt).Group
StartAt = i + 1
End If
Next i
End With

End Sub

khalid79m
03-15-2008, 06:55 AM
thanks , works perfect, I may need a slight tweek will be in touch.. thanks you guys are life savers.

khalid79m
03-25-2008, 05:43 AM
Public Sub ProcessData3()

Dim LastRow As Long
Dim StartAt As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartAt = 3

For i = 4 To LastRow

If LCase(.Cells(i, "AC").Value) = "3" Then
.Rows(StartAt).Resize(i - StartAt).Group
StartAt = i + 1
End If
Next i

End With

End Sub

need help again

I have a massize spreadsheets which has staff scores in.

my coloumns are as follows
Site
TeamManager
TeamLeader
Staff
Staff Score


I need to group by site , tm , tl can anyone help ? in coloumn ac I have the number 1 next to all sites that i want to group but WITHIN that group I have and number 2 next all the teammanagers and 3 next to all the team leaders i want to group.

Can anyone help ?

Paul_Hossler
03-25-2008, 07:40 AM
A pivot table would probably allow you to group and subtotal your data

Could you post a SMALL sample workbook?

Paul