# Group rows based on contents of two columns

#### MurrayBarn

##### New Member
I have a spreadsheet that is an income statement for the year with each month showing separately in its own column. The budgeted figures are also shown monthly per column. On the far right, I have a total for both Actual (in column AO) and Budget (AP). I am trying to find a macro code that will Group all rows that both AO and AP are zero. The formulas in AO are =C3+F3+I3..... for twelve months. The Budget total has a similar formula but refers to the columns just to the right of the Actual columns.

I am new at Macros and have found a lot of macros that have very complicated range definitions etc and I cant get any to work for me. The row range is 9:300.

I could really do with some help to firstly Ungroup any rows that were previously Grouped and then to Group the rows that fit the above definition. Also, I have multiple spreadsheets and I need this done for all four. Their names could be Apple, Orange, Pear and Banana

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Not tested.

Code:
``````Sub GroupZeros()
Dim SheetList As Variant
Dim WS As Worksheet
Dim A As Long
Dim aCell As Range
Dim StartRow As Long

SheetList = Split("Apple,Orange,Pear,Banana", ",")
For A = 0 To UBound(SheetList)
Set WS = Worksheets(SheetList(A))
With WS
.Range("A1").CurrentRegion.Ungroup
For Each aCell In .Range("AO2:AO330")
If aCell.Value = 0 And aCell.Offset(, 1) = 0 Then
If StartRow = 0 Then
StartRow = aCell.Row
End If
Else
If StartRow <> 0 Then
.Range("AO" & StartRow & ":AP" & aCell.Row).Group
StartrRow = 0
End If
End If
Next
End With
Next

End Sub``````

Thank you David

I'll give that a bash and let you know how it goes!

Hi David

It doesnt work. It stops at .Range("A1").CurrentRegion.UnGroup with a 1004 Runtime error - Ungroup method of Range class failed.

As I am still a noob, I was unable to debug it. Perhaps you could help by simplifying it a lot for me. If you could assist by taking out the Ungroup section and just helping me get the Macro to select the rows that have a formula answer of zero to be hidden. Iam trying to get the below to work, but I am struggling with the syntax of how to get the Range to be the row that has an answer of zero in both Column AO and AP:

Sub GroupZeroLine()
Dim aCell As Range
Dim StartRow As Long

Application.Workbooks("Test.xlsm").Worksheets("Banana").Activate
For Each aCell In Range("AO2:AO330")
If aCell.Value = 0 And aCell.Offset(, 1) = 0 Then
If StartRow = 0 Then
StartRow = aCell.Row
End If
Else
If StartRow <> 0 Then
Range("AO" & StartRow & ":AP" & aCell.Row).Group
StartRow = 0
End If

End If
Next

End Sub

I get caught with
Range("AO" & StartRow & ":AP" & aCell.Row).Group
I see you had a . in front of Range which it also didnt seem to like

Any chance you can send me a sample? tinbendr at gmail (Or post a link using a service like box.com or google docs)

You might try

Code:
`` .Cells.Ungroup``

I was just trying to ungroup all before starting to group again.

I get caught with
Telling me the error message would help.

I see you had a . in front of Range which it also didnt seem to like
This qualifies the object. If you don't put those in, Excel uses the active sheet, which might product strange results.

Dunno if this helps - I can't find a way to upload an Excel file.

So I have rows with income statement data per month laid out as Actual/Budget/Variance across the columns per month. In Column AO and AP are the sums of the Actual Columns and the Budget Columns. I want to Group all rows where AO and AP have a zero in it. If it is blank it must not Group it. I just need a very simple Loop function to do this. I will add to the macro when that works well

 Total Actual Budget Variance Actual Budget Variance Actual Budget Variance Actual Budget Variance Actual Budget Variance Actual Budget Variance Actual Budget Variance Actual Budget Variance Actual Budget Variance Actual Budget Variance Actual Budget Variance Actual Budget Variance Actual Budget Variance 128300 Salaries - Basic 160,050 168,382 8,332 203,087 168,382 (34,705) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 363,137 336,763 (26,374) 128315 Salaries - Basic JLS - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 128320 Salaries - Basic Admin - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 128325 Salaries - Basic Marketing - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 128330 Salaries - Basic Directors - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 128375 Salaries - Other 8,940 11,566 2,626 356,045 16,685 (339,360) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 364,985 28,251 (336,735) 128388 Salaries - Other JLS - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 128389 Salaries - Other Admin - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 128390 Salaries - Other Marketing - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 128391 Salaries - Other Directors - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 128400 Salaries - Anc Bush 21,343 21,890 546 25,414 21,890 (3,524) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 46,757 43,779 (2,978) 128415 Salaries - Anc JLS - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 128420 Salaries - Anc Admin - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 128425 Salaries - Anc Marketing - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 128430 Salaries - Anc Directors - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 130000 Wages Basic - Bush 212,602 209,497 (3,105) 212,351 209,497 (2,854) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 424,953 418,994 (5,959) 130015 Wages Basic - JLS - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 130050 Wages - Overtime & Other - Bush 18,082 32,992 14,910 39,145 42,362 3,217 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 57,227 75,354 18,127 130065 Wages - Overtime & Other - JLS - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 130100 Wages - Anc Bush 18,412 18,855 443 18,463 18,855 392 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 36,874 37,709 835 130115 Wages - Anc JLS - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 112400 Skills Development Levies - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - TBA Unemployment Insurance - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - TBA Workmens Compensation - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

<colgroup><col span="2"><col><col span="40"></colgroup><tbody>
</tbody>

<tbody>
</tbody>

This does work, but I don't think it gives the result you're looking for.

Code:
``````Sub GroupZeros()
Dim SheetList As Variant
Dim WS As Worksheet
Dim A As Long
Dim aCell As Range
Dim StartRow As Long

SheetList = Split("Sheet1,Sheet2,Sheet3", ",")
For A = 0 To UBound(SheetList)
Set WS = Worksheets(SheetList(A))
With WS
Cells.ClearOutline
For Each aCell In .Range("AO2:AO330")
If aCell.Value = 0 And aCell.Offset(, 1) = 0 Then
If StartRow = 0 Then
StartRow = aCell.Row
End If
Else
If StartRow <> 0 Then
.Rows(StartRow & ":" & aCell.Row).Group
StartrRow = 0
End If
End If
Next
End With
Next

End Sub``````

Maybe I've got it.

Code:
``````Sub GroupZeros()
Dim SheetList As Variant
Dim WS As Worksheet
Dim A As Long
Dim aCell As Range
Dim StartRow As Long

SheetList = Split("Sheet1,Sheet2,Sheet3", ",")
For A = 0 To UBound(SheetList)
Set WS = Worksheets(SheetList(A))
With WS
Cells.ClearOutline
For Each aCell In .Range("AO2:AO330")
If aCell.Text = "0" And aCell.Offset(, 1).Text = "0" Then
If StartRow = 0 Then
StartRow = aCell.Row
End If
Else
If StartRow <> 0 Then
.Range("AO" & StartRow & ":AP" & aCell.Row - 1).Rows.Group
StartRow = 0
End If
End If
Next
End With
Next

End Sub``````

Re: Maybe I've got it.

Hi David

Thanks again.

I have run the code and it is doing something and not throwing out any errors, however, when it is finished, it does not appear to have Grouped anything as the little 1 and 2 are not visible on the top left of the screen if you manually Grouped some rows. Any ideas?

Re: Maybe I've got it.

Hi David

Got it!!! The Cells.ClearOutline is in the Loop. I have taken it out and it works!! Thank you so much!

Could you please explain a little more how the Split() function works. For instance, if I have four Worksheets that have data I want to view and another four data worksheets, I assume that if I define them as you have, it will only work on the sheets defined?

Kind regards
Murray

Replies
13
Views
149
Replies
5
Views
266
Replies
0
Views
332
Replies
3
Views
123
Replies
1
Views
368

1,211,987
Messages
6,105,226
Members
447,957
Latest member
Basildon

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back