CODEWITHSUNDEEP
excelvba
Qwin
Qwin

Reputation: 3

VBA Excel - Group rows based on specific criteria

I'm very new to VBA and I'm trying to create code in Excel which will select rows based on values in a range (eg. 0's) and group them together using the group function in Excel. In the linked captures below you can see where I start (Capture 1) to where I want to go (Capture 2).

The following code is where I've got to which selects all rows which are 0. Unfortunately, I cannot use the Group function when multiple rows are selected that are not together.

I'm not quite sure what to add into the code to make it group the first set of 0's then move to the next set of 0's and do the same until it reached the end of the range. I would also like it to skip blank cells such as that in row 13 of the screen captures attached. 

Sub SelectGroupRows0()

Dim c As Range
Dim rng0 As Range
Dim block As Range

Set block = Range("B4:B23")

For Each c In block
    If c = 0 Then
        If rng0 Is Nothing Then Set rng0 = c.EntireRow
        Set rng0 = Union(rng0, c.EntireRow)

    Else: End If
Next c

rng0.Select
Selection.Rows.Group

End Sub

Any help on this would be greatly appreciated.

Thanks in advance,

Q

enter image description here enter image description here

Upvotes: 0

Views: 6309

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

You had a few Else If not in the right place.

Also, you need to loop through the Union Range Areas to group each one of them.

Note: You don't need to use rng0.Select and later Selection.Rows.Group.

Code

Option Explicit

Sub SelectGroupRows0()

Dim c As Range
Dim rng0 As Range
Dim block As Range

Set block = Range("B4:B23")

For Each c In block
    If Not IsEmpty(c) And c.Value = 0 Then
        If rng0 Is Nothing Then
            Set rng0 = c
        Else
            Set rng0 = Union(rng0, c)
        End If
    End If
Next c

' loop through your Range's Areas and group each one of them
For Each c In rng0.Areas
    c.EntireRow.Group
Next c

End Sub

Upvotes: 1

Related Questions