Can I automatically group and hide cells with zero?

[Ben Thiele]

I often end up with multiple column lists in different sheets in the same workbook. I use a macro to sort them all in descending order, which leaves me with a handful of zeros at the bottom of each list. I then manually click from one sheet to another and group the zero cells and hide the group. The time spent doing this grouping and hiding adds up, so I'm wondering if Chandoo knows a way to automatically find and group cells containing a 0?

 

[Luke M]

Hi Ben,
You're wanting the macro to do the sorting and hiding, I presume? To clarify, you were using the word "Group" which is a function within XL, like a roll-up. Is that really what you want, or do you just hide the rows?

Are the 0's all in col A, or in a different column, or would the macro need to scan all columns?

 

[Ben Thiele]

Hi Luke,

Thanks for getting back to me. I specifically want the macro to perform the group function (which I usually do with the shortcut ALT+SHIFT+→).

Let's assume there are names in column A and numbers in column B. Only column B determines which rows get grouped and hidden.

Best,
Ben

 

[Luke M]

This should at least get you started then.

Sub SortAndGroup()
Dim lastRow As Long
Dim fCell As Range
Application.ScreenUpdating = False
With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    'Setup the sort
    With .Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("B1"), SortOn:=xlSortOnValues, _
            Order:=xlDescending, DataOption:=xlSortTextAsNumbers
        .SetRange Range("A1", Cells.SpecialCells(xlCellTypeLastCell))
        .Header = xlGuess
        .Orientation = xlTopToBottom
        .Apply
    End With
    'Check if there are any 0's
    Set fCell = .Range("B:B").Find(what:=0, after:=Range("B1"))
    If Not fCell Is Nothing Then
        'If so, group them
        .Range(fCell, .Cells(lastRow, "A")).Rows.Group
        .Outline.ShowLevels RowLevels:=1
    End If
End With
Application.ScreenUpdating = True
End Sub

 

[Ben Thiele]

Luke! Thanks so much, it worked on my simple Column A, Column B situation, and I'll be able to adapt it to other situations.

Much appreciated,
Ben

 

[Ben Thiele]

Hi Luke,

I'm trying to adapt your VBA code to the situation below, and I'm hung up on a problem. First let's say Metric 1 is in column B and Metric 2 is in Column C. I only want to sort and group rows based on Column C. When Excel performs this part of the code: Set fCell = .Range("C:C").Find(what:=0, after:=Range("C1")), I think it finds the first zero after C1 in any column, not just Column C.

As a result I end up with the rows of Item B, C, and D grouped together when I only want the rows of Item C and D grouped together because they have a zero under Metric 2. What do you think?

​

​

Metric 1 Metric 2
Item A 1 9
Item B 0 5
Item C 2 0
Item D 1 0
Item E 8 10​

 

[Ben Thiele]

 

[Luke M]

Correct code, with a few comments. Biggest error was that on the Find, need to specify to look at whole cell, not part, due to the 0 being in 10. Doh!

Sub SortAndGroup()
Dim lastRow As Long
Dim fCell As Range
Application.ScreenUpdating = False
With ActiveSheet
lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    'Setup the sort
   With .Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("C1"), SortOn:=xlSortOnValues, _
            Order:=xlDescending, DataOption:=xlSortTextAsNumbers
        'Assume you want to keep all the data lined up, so need to sort
        'the range of A:C
        .SetRange Range("A1", Cells.SpecialCells(xlCellTypeLastCell))
        .Header = xlGuess
        .Orientation = xlTopToBottom
        .Apply
    End With
    'Check if there are any 0's
    'Edited this to look at entire cell. Was mistakenly finding 0 in "10"
   Set fCell = .Range("C:C").Find(what:=0, after:=Range("C1"), lookat:=xlWhole)
    If Not fCell Is Nothing Then
        'If so, group them
       .Range(fCell, .Cells(lastRow, "C")).Rows.Group
        .Outline.ShowLevels RowLevels:=1
    End If
End With
Application.ScreenUpdating = True
End Sub

 

[Ben Thiele]

Thanks Luke!

Another detail is that I had a sum row below the numeric columns that I didn't want to get sorted or grouped or hidden. I was able to accomplish this by using a simple range such as
.SetRange Range("A2:C453")
rather than
.SetRange Range("A1", Cells.SpecialCells(xlCellTypeLastCell))

 

[Luke M]

Good to know!