Group rows based on contents of two columns

MurrayBarn

New Member
Joined
May 27, 2012
Messages
27
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Tinbendr

Well-known Member
Joined
Jul 21, 2010
Messages
997
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
 

MurrayBarn

New Member
Joined
May 27, 2012
Messages
27
Office Version
  1. 365
Platform
  1. Windows
Thank you David

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

MurrayBarn

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

Tinbendr

Well-known Member
Joined
Jul 21, 2010
Messages
997

ADVERTISEMENT

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.
 

MurrayBarn

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

Tinbendr

Well-known Member
Joined
Jul 21, 2010
Messages
997

ADVERTISEMENT

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
 

Tinbendr

Well-known Member
Joined
Jul 21, 2010
Messages
997
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
 

MurrayBarn

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

MurrayBarn

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

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

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
Top