How to combine two seperate macros

[elax29]

Hello,
I have the following two macros which separately work but when I run one and then the next, they don't to build on top of each other and work in unison. running macro 2 undoes macro 1 and vice versa.
Essentially what I need is:
a) hide entire rows where individual row total = 0 (Macro 1)
b) in addition to this, hide entire rows included in named ranges where total of the named range = 0 (Macro 2)
c) unhide all of the above if the information totals > 0
In the below case, combined macro would hide row 3 (0 total) and hide rows 7-11 (George family named range as total of range = 0)

Thanks in advance!

Here is the data:

[TABLE="width: 324"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Named Range[/TD]
[TD]Name[/TD]
[TD]Total Hours[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Smith[/TD]
[TD]Smith Family[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Smith[/TD]
[TD]Mary[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Smith[/TD]
[TD]Heather[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Smith[/TD]
[TD]Total Smith[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]George[/TD]
[TD]George Family[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]George[/TD]
[TD]John[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]George[/TD]
[TD]Mary[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]George[/TD]
[TD]Heather[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]George[/TD]
[TD]Total George[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Spencer[/TD]
[TD]Spencer Family[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Spencer[/TD]
[TD]John[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Spencer[/TD]
[TD]Mary[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Spencer[/TD]
[TD]Heather[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Spencer[/TD]
[TD]Total Spencer[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

Macro 1 - purpose is to hide rows where row total = 0. Unhide if data changes to > 0 ( i.e. would hide rows 3 and 8-11)

Sub Hide_H()
Application.ScreenUpdating = False
Dim LastRow As Long, c As Range
Application.EnableEvents = False
LastRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
On Error Resume Next
For Each c In Range("D1:D" & LastRow)
If c.Value > 0 Or c.Value = "" Then
c.EntireRow.Hidden = False
ElseIf c.Value = 0 Then
c.EntireRow.Hidden = True
End If
Next
On Error GoTo 0
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Macro 2 - would hide entire named range if total of range = 0. Unhide if data changes to > 0 (i.e. would hide rows 7-11 George Family as total of range = 0
[TABLE="width: 350"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Sub NamedRange()[/TD]
[/TR]
[TR]
[TD]Smith = WorksheetFunction.Sum(Range("Smith"))[/TD]
[/TR]
[TR]
[TD]George = WorksheetFunction.Sum(Range("George"))[/TD]
[/TR]
[TR]
[TD]Spencer = WorksheetFunction.Sum(Range("Spencer"))[/TD]
[/TR]
[TR]
[TD] If Smith = 0 Then[/TD]
[/TR]
[TR]
[TD] Range("Smith").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] ElseIf Smith > 0 Then[/TD]
[/TR]
[TR]
[TD] Range("Smith").EntireRow.Hidden = False[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If George = 0 Then[/TD]
[/TR]
[TR]
[TD] Range("George").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] ElseIf George > 0 Then[/TD]
[/TR]
[TR]
[TD] Range("George").EntireRow.Hidden = False[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] If Spencer = 0 Then[/TD]
[/TR]
[TR]
[TD] Range("Spencer").EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD] ElseIf Spencer > 0 Then[/TD]
[/TR]
[TR]
[TD] Range("Spencer").EntireRow.Hidden = False[/TD]
[/TR]
[TR]
[TD] End If[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[CalcSux78]

I took a different approach hiding as I looped through all rows in the data:

Sub HideSome()
Dim R As Integer
Dim i As Integer
Dim iFirst As Integer   [COLOR=#008000]'Start of Family[/COLOR]

R = Cells(Rows.Count, 1).End(xlUp).Row  [COLOR=#008000]'last row in data set[/COLOR]
For i = 2 To R
    If Len(Cells(i, 3)) = 0 And Cells(i, 3) = 0 Then
        [COLOR=#008000]'Col B, Row i value contains Family, mark for later[/COLOR]
        iFirst = i
    ElseIf Len(Cells(i, 3)) = 1 And Cells(i, 3) = 0 Then
        If InStr(1, Cells(i, 2).Value, "[COLOR=#0000ff]Total[/COLOR]", vbTextCompare) > 0 Then
           [COLOR=#008000] 'family total = 0, hide[/COLOR]
            Cells(iFirst, 3).EntireRow.Hidden = True
        End If
        Cells(i, 3).EntireRow.Hidden = True
    End If
Next i
End Sub

If your real data doesn't contain the word "Total" in column B, then replace the blue highlighted "Total" in the above code with correct word.

 

[elax29]

I took a different approach hiding as I looped through all rows in the data:

Sub HideSome()
Dim R As Integer
Dim i As Integer
Dim iFirst As Integer   [COLOR=#008000]'Start of Family[/COLOR]

R = Cells(Rows.Count, 1).End(xlUp).Row  [COLOR=#008000]'last row in data set[/COLOR]
For i = 2 To R
    If Len(Cells(i, 3)) = 0 And Cells(i, 3) = 0 Then
        [COLOR=#008000]'Col B, Row i value contains Family, mark for later[/COLOR]
        iFirst = i
    ElseIf Len(Cells(i, 3)) = 1 And Cells(i, 3) = 0 Then
        If InStr(1, Cells(i, 2).Value, "[COLOR=#0000ff]Total[/COLOR]", vbTextCompare) > 0 Then
           [COLOR=#008000] 'family total = 0, hide[/COLOR]
            Cells(iFirst, 3).EntireRow.Hidden = True
        End If
        Cells(i, 3).EntireRow.Hidden = True
    End If
Next i
End Sub

If your real data doesn't contain the word "Total" in column B, then replace the blue highlighted "Total" in the above code with correct word.

Thanks very much for the response. It seems to work except for a few issues which I'm hoping you might be able to help with:
a) Column B doesn't actually exist in the data. the data consists of Column C + D. I put in Column B to outline to you the names of the named ranges. When I take column B out of the macro and adjust the column numbers accordingly, it doesn't work
b) there doesn't seem to be an unhide function for when the rows and/or named ranges >0. when I adjusted the data and re-ran the macro, the lines remained hidden.

Thanks so much in advance!

 

[CalcSux78]

Ooops, I overlooked the column references in your first post.

so I adjusted my first post to reference the correct column indexes and also added a line to unhide all rows before getting into the code to allow the user to run the script multiple times.

Sub HideSome()
Dim r As Integer
Dim i As Integer
Dim iFirst As Integer   'Start of Family
Dim iLast As Integer    'end of family

r = Cells(Rows.Count, 2).End(xlUp).Row  'last row in data set
[COLOR=#0000ff]'unhide rows
Rows("1:" & r).Hidden = False[/COLOR]
For i = 2 To r
    If Len(Cells(i, 4)) = 0 And Cells(i, 4) = 0 Then
        'Col C, Row i value contains Family, mark for later
        iFirst = i
    ElseIf Len(Cells(i, 4)) = 1 And Cells(i, 4) = 0 Then
        If InStr(1, Cells(i, 3).Value, "Total", vbTextCompare) > 0 Then
            'family total = 0, hide
            Cells(iFirst, 4).EntireRow.Hidden = True
        End If
        Cells(i, 4).EntireRow.Hidden = True
    End If
Next i
End Sub

 

[elax29]

thanks CalcSux78. It does work with my sample data. Just so I'm clear, the macro performs the following:
go row by row
a) if row in column D contains 0 characters and equals 0 (this would be blank row)
b) OR if row in column D contains 1 character and cell equals 0 (this would be a row with a value 0 and above)
c) THEN search for word Total in Column C and hide entire Total row if True
d) OR hide entire row if A or B true

The reason that I ask is that my actual data contains blank separator rows between each of the lines. I would ideally like for these separator lines to also hide if the line above = 0 and is going to be hidden. When I run the macro, all the blank separator lines remain. N/B that my original macro didn't hide the blank lines either as I couldn't figure out how to incorporate that into the macro at the time.

The macro that you so kindly created also doesn't hide the title lines in my actual data which is my mistake for not outlining it correctly above. In the data example above, using your macro, it hides the information as the title row for each family in column D is blank (i.e Smith Family row in Column D is blank). However in my actual data the headings are repeated in each family section i.e "Name of Family" (column C) + "Total Hours (Column D) are in each family section. So when I run the macro it doesn't hide the line because the Column D row isn't blank, nor does it = 0. Is it possible to fix this also perhaps?

Sorry for all the amendments to my original information. I'll try to be more thorough next time...

 

[CalcSux78]

To remove the extra separator line when a group of rows are hidden: Change the line within the ElseIf statement from Cells(iFirst, 4).EntireRow.Hidden = True to Rows(iFirst - 1 & ":" & iFirst).Hidden = True. At this section of the sub, the entire group is hidden except for the title row, so we are hiding the row before the title and the title row.

...[ So when I run the macro it doesn't hide the line because the Column D row isn't blank, nor does it = 0. Is it possible to fix this also perhaps?
/QUOTE]

What kind of info is in column D for the Name of family row?

The location you are going to need to tweek is the first IF statement in the FOR loop. At this location, we assign a value to iFirst (which is needed to determine what row number is the title row for each group.) You should be able to adjust the statement to match your actual data leaving the rest of the code as is to regain functionality.

To break down how this sub works:

• Unhide all rows
• Find the range of data to work with and loop through all rows
  • determine if current row within loop is a title row, spacer row, row with good data(no hide), or data
  • change visibility based off of type of row

 

[jones212]

interesting thread, I too was having the same problem

 

[elax29]

...
What kind of info is in column D for the Name of family row?

Hi CalcSux78. In answer to your question above, it is text that is in Column D for the Name of Family Row. It says "Total Hours".

Thanks also for the breakdown of what the macro does. Will try and implement your suggestions and see how I go...