Group rows based on contents of two columns

MurrayBarn

New Member
Joined
May 27, 2012
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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

Please help somebody!
 

Excel Facts

Shade all formula cells
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
 
Upvote 0
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

I really appreciate your help.
 
Upvote 0
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.
 
Upvote 0
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

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

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

<tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top