• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA for conditional grouping of certain rows

B

Berry

New Member
Hi All,

I am looking for a code that can 'group' certain rows in case a certain cell in that row (for example column A) contains a specific code (for example, '999'). In case the next cell in column A contains this specific code as well it should also become part of that group, but if it hasn't the group should 'stop'.

Probably the code I am looking for is pretty basic, but a quick search on the internet did not get me anywhere.

Thanks for your reply!
 
Not quite what you asked, but does the built in Data - Outline - Subtotal create what you want? It will automatically group things based on a specified column.

If you do still want to go the macro route, this hsould help you get started at least.
Code: 
Sub GroupRows()
Dim myValue As Variant
Dim myCol As String
Dim myRow As Long
Dim startRow As Long
Dim lastRow As Long
'Which column to look at?
myCol = "A"
'Which row to look at first?
myRow = 2
lastRow = Cells(myRow, myCol).End(xlDown).Row
myValue = Cells(myRow, myCol)
startRow = myRow
Application.ScreenUpdating = False
For i = myRow + 1 To lastRow + 1
  If Cells(i, myCol).Value <> myValue Then
  'New group begins
  Range(startRow + 1 & ":" & i - 1).EntireRow.Group
  startRow = i
  myValue = Cells(i, myCol).Value
  End If
  If myValue = "" Then
  'blank cell
  Exit For
  End If
Next i
Application.ScreenUpdating = True
End Sub
 
Hi Luke M,

A couple of issues in the code above.
1. The beginning row of the new group does not get grouped. For example, if the data is
A
A
A
Only the 2nd and 3rd Row gets grouped.

2. The grouping after the first group takes a hit completely after that. For example,
A
A
B
C
Except the first, all the other gets grouped since the condition is if the current cell is not equal to above, then group it.

Please let me know your thoughts. I hope I am understanding the requirement correctly.
 
Hi Berry,

The below might work for you. Have a try on this.
Code: 
Sub Grup()
Dim row As Long
Dim col As String
Dim start_row As Long
Dim i As Long, counter As Long
'Input the beginning row
start_row = 1
'Input the Column name
col = "A"
counter = 0
row = ActiveSheet.Cells.Find(What:="*", After:=Cells(Cells.Rows.Count, Cells.Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
For i = start_row + 1 To row
If Cells(i, col).Value = Cells(i - 1, col).Value Then
Cells(i, col).EntireRow.Group
  If counter = 0 Then
  Cells(i - 1, col).EntireRow.Group
  counter = counter + 1
  Else: End If
Else
counter = 0
End If
Next i
End Sub


Be careful though.. it wont work in cases like below.
A
A
A
B
B
C
 
Last edited by a moderator:
Hmm. Would you mind posting a small sample of what your data looks like, and what you would like it to look like?
 
@BBD
Hi!
When posting code, would you please put it into the proper container? Check the ribbon, it's the 5th icon from the right, the previous to the quote one.
Regards!
 
Hi,
I also need something like that, I am working on a project where I need to group all rows into 3 levels. Idea is that 1st level would be regional grouping, than in each region to group by manufacturer, and in 3rd level for every manufacturer to group by its brands. If anyone has an idea how to code this, please help, it would mean a lot to me.
 
Hi ,

I am not clear on what you want the output to look like ; will a simple multi-level sort not do what you want ?

Can you upload a sample workbook , with enough data in it , and show in an output tab , how you want the output to look like ?

Since this thread is quite old , it would be nice if you could start a new thread of your own with these details in it.

Narayan
 
You must log in or register to reply here.
Back
Top