Auto hide rows

[stretchgre]

Hello

I have a series of rows that I need hidden on multiple tabs. It is a financial spreadsheet and below is an example of row columns. For simplicity sake is there a way I can hide using row names so every sheet hides the same rows?

I need to hide about 10 rows on each page but they all have the same name.
[TABLE="width: 248"]
<colgroup><col width="331" style="width: 248pt; mso-width-source: userset; mso-width-alt: 12105;"> <tbody>[TR]
[TD="class: xl72, width: 331, bgcolor: transparent"]Revenue Per Call[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Cost Per Call (ALL Labor)[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]Total Working Labor Minutes per Call[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]Success Per Lead[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Revenue Per Lead[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]Revenue Per Success[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]Total Working Labor Minutes per Success[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]Revenue Per Working Hour[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]Gross Profit Per Working Hour (All Labor)[/TD]
[/TR]
</tbody>[/TABLE]

 

[mc5476]

The only way that I can think would be placing code in each of the worksheet_activation events that hide the entire row based on the value of those cells.

For Example:
Sheets("NameOfWorksheet").Rows(FirstRowToHide).EntireRow.Hidden = True
Sheets("NameOfWorksheet").Rows(SecondRowToHide).EntireRow.Hidden = True
Sheets("NameOfWorksheet").Rows(ThirdRowToHide).EntireRow.Hidden = True
...etc.

 

[Peter_SSs]

Your thread title is "Auto hide rows". Can you explain what you want/expect with regard to the word "Auto"?

 

[stretchgre]

i hide and unhide the same row names on each tab of a worksheet. So i want a way to easily hide or unhide them all without having to manually do it.

 

[Fluff]

If the rows are in the same place on each sheet you could use something like this

Sub Hider()

    With ActiveSheet.Range("[COLOR=#ff0000]A3:A5,A7,A11[/COLOR]").EntireRow
    .Hidden = Not .Hidden
    End With

End Sub

changing the ranges as neccessary.

 

[stretchgre]

They are not in the same places on each sheet. Some sheets have more information. that is why i am asking if it can be done by row name

 

[Fluff]

When you say you want it done by row name.
What exactly do you mean, are they named ranges, or they simply values in Col A?

 

[stretchgre]

Sorry the values in Column A. Some examples are listed in the original post.

 

[Fluff]

Give this a go, it will loop through all sheets looking for your values & if the rows are hidden, they will become visible & vice versa

Sub Hider()

    Dim Ws As Worksheet
    Dim Nms() As Variant
    Dim Nm As Variant
    
    Nms = Array("[COLOR=#ff0000]chrX[/COLOR]", "[COLOR=#ff0000]chr20[/COLOR]", "[COLOR=#ff0000]chr5[/COLOR]")
    
    For Each Ws In Worksheets
        For Each Nm In Nms
            On Error Resume Next
            With Ws.Columns(1).Find(What:=Nm, After:=Ws.Range("A1"), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).EntireRow
                    .Hidden = Not .Hidden
            On Error GoTo 0
            End With
        Next Nm
    Next Ws
End Sub

You'll need to replace the values in red with your row names

 

[stretchgre]

I have done what you asked and nothing is happening

Sub Hider()


Dim Ws As Worksheet
Dim Nms() As Variant
Dim Nm As Variant

Nms = Array("Leadership Cost (Labor Hours/Subtotal * Allocation)", "Gross Profit (Specialists Only", "Gross Profit (Direct Labor Costs)")

For Each Ws In Worksheets
For Each Nm In Nms
On Error Resume Next
With Ws.Columns(1).Find(What:=Nm, After:=Ws.Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).EntireRow
.Hidden = Not .Hidden
On Error GoTo 0
End With
Next Nm
Next Ws
End Sub