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.

Revenue Per Call

Cost Per Call (ALL Labor)

Total Working Labor Minutes per Call

Success Per Lead

Revenue Per Lead

Revenue Per Success

Total Working Labor Minutes per Success

Revenue Per Working Hour

Gross Profit Per Working Hour (All Labor)

<colgroup><col width="331" style="width: 248pt; mso-width-source: userset; mso-width-alt: 12105;"> <tbody> </tbody>

 

[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