need a macro to group rows that contain '-'

[tonyjyoo]

I need a macro that groups rows that contain the '-' character (in column C:C)

Not hide, the + sign in Data -> group.

 

[shknbk2]

Try this:

Sub groupRows()
    Dim row1 As Integer, row2 As Integer
    Dim colC As Integer
    row2 = 1
    colC = Cells(Rows.Count, 3).End(xlUp).Row
    Do
        row1 = row2
        While Cells(row1, 3).Value <> "-"
            row1 = row1 + 1
            If row1 > colC Then Exit Do
        Wend
        row2 = row1 + 1
        While Cells(row2, 3).Value = "-"
            row2 = row2 + 1
        Wend
        Range(Cells(row1, 3), Cells(row2 - 1, 3)).EntireRow.Group
    Loop
End Sub

 

[tonyjyoo]

Try this:

Sub groupRows()
    Dim row1 As Integer, row2 As Integer
    Dim colC As Integer
    row2 = 1
    colC = Cells(Rows.Count, 3).End(xlUp).Row
    Do
        row1 = row2
        While Cells(row1, 3).Value <> "-"
            row1 = row1 + 1
            If row1 > colC Then Exit Do
        Wend
        row2 = row1 + 1
        While Cells(row2, 3).Value = "-"
            row2 = row2 + 1
        Wend
        Range(Cells(row1, 3), Cells(row2 - 1, 3)).EntireRow.Group
    Loop
End Sub

didnt work.

is it because my cells with - in column C are displaying it as zero as a formula?

 

[shknbk2]

Could be. Go through the code step by step (F8) and when you're on a line with a "-" result, see what the value reads as in the code. Then change the <> "-" to that.

 

[tonyjyoo]

looks like its because it's a formula. how can i work around this?

 

[shknbk2]

what does it say the value is for those cells?

 

[tonyjyoo]

it doesn't show anything?

 

[shknbk2]

In the workbook, select a cell that has the -. Then, in the VB window, type this into the Immediate Window (Ctrl+G) and hit Enter:

?"'" & Activecell.Value & "'"

What is the result in the Immediate window directly below the line you typed?

For example, in a cell that has the formula =Rand(), the value I get is '0.481588996523121' in the window.

 

[tonyjyoo]

I figured it out, updated code because it was reading "0". Thanks.

One last thing, how can I adjust the macro to NOT group subtotal rows that have "0"?

 

[shknbk2]

change the <> 0 and the = 0 around (e.g., change the <> 0 to = 0, and change the = 0 to <> 0).

I suppose you could instead add a NOT in each line rather than the above: While Not Cells(row1, 3).Value <> 0, etc.