macro to hide cells in multiple sheets

**shutter** · 09-18-2011, 06:49 PM

Hello

I wonder if you can offer some assistance please.
I am trying to create some code that will look at a range of cells and if any of them contain a tigger ( so a 0 or a 1) then the whole row is hidden. The problem I have is that I have 25 sheets I need to do this over and the methods I have been looking at are long and cumbersome!

I would like to try and get away with the quickest processing option available if possible!

Here is what I have been trying:

Sub HideRt1()
On Error Resume Next
With Range("Rt1!K9:K43")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
End Sub

I can repeat this for all of the sheets and then have one macro to call them all, but it is long and rather slow. After 2 minutes it is still at around sheet 15!! eek!

On another macro I have an array set up to perform a different task, but I am having problems in adapting it to suit my above requirements. The other macro look a little like this..

Sub Step4_CleanUp()

Application.ScreenUpdating = False
   Dim i As Variant
        
    Dim myArray As Variant
    myArray = Array("St1", "St2", "St3", "St4", "St5", "St6", "St7", "St8", "St9", "St10", "St11", "St12", "St13", "St14", "St15", "St16", "St17", "St18", "St19", "St20", "St21", "St22", "St23", "St24", "St25")
        
    For Each i In myArray
    Dim c As Range
    For Each c In Range("K9:K43")

    ** Do some funky business **

    Next c
    Next
          
Application.ScreenUpdating = True
End Sub

If anyone is able to help me out I would greatly appreciate it. I think the problem really isthe looping all the time and this is slow, so an alternative method without macro - perhaps some type of validation or conditioning is also viable if you can direct me.

Thanks alot!
Chris

**TMShucks** · 09-19-2011, 06:50 AM

I'm not 100% sure which range(s) you are trying to work with in that, at one point, you limit the range to K9:K43 and, elsewhere, you go from 1 to ".rows.count".

In the context of:

With Range("Rt1!K9:K43")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count

I think i will take values from 1 to 35 which, possibly, is not what you meant.

?Range("K9:K43").Rows.Count
 35

Anyway, the following code will unhide the rows between K9 and K43 and then hide any rows between 1 and the last row in column K where the value of the row is 0.

Sub HideSheets()
Dim AWF As WorksheetFunction: Set AWF = WorksheetFunction
Dim i As Long
Dim SheetName As Variant
Dim myArray As Variant
myArray = Array("St1", "St2", "St3", "St4", "St5", "St6", "St7", "St8", "St9", "St10", "St11", "St12", "St13", "St14", "St15", "St16", "St17", "St18", "St19", "St20", "St21", "St22", "St23", "St24", "St25")
'myArray = Array("St1", "St2", "St3", "St4")    ' for testing

Application.ScreenUpdating = False
On Error Resume Next

For Each SheetName In myArray
    With Sheets(SheetName)
        With .Range("K9:K43")
            .EntireRow.Hidden = False
        End With
        For i = 1 To .Range("K" & .Rows.Count).End(xlUp).Row
            If AWF.Sum(.Rows(i)) = 0 Then
                .Rows(i).EntireRow.Hidden = True
            End If
        Next i
    End With
Next 'SheetName

On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Regards

**shutter** · 09-19-2011, 06:45 PM

Thanks for your repsonse!
Sorry for a being a little confusing - I am confused myself. I don't need to count anything, as I will know what rows I want to look at everytime.

Basically I need a macro to look at 25 sheets (hence the array of sheets), and in each one of these have a look in the range of K9:K43. If in any of these cells within that range, there is a number 0 then hide that row, otherwise it will have a number 1 and so has to ensure that row is unhidden.

Does that make it clearer? As previously said, I don't mind having a lot of code, as long as it is the quickest way of achieving the end result - I cannot think of another way of doing it without using code!

Thanks alot

**shutter** · 09-19-2011, 07:35 PM

Perhaps it may help to add that the reason I do not want some rows showing is because they have data generated in there that I don't want printed, and so these rows will not need to be printed, so perhaps setting the print area from the top of the page down to the bottom row of occupied data could also be an option!?...

**TMShucks** · 09-19-2011, 10:01 PM

Then why are you summing the rows? If K9:K43 have a value of 0 or not determines whether you hide the row or not, then that's all you need to check.

        For i = 9 To .43
            If .Range("K" & i) = 0 Then
                .Rows(i).EntireRow.Hidden = True
            End If
        Next i

Regards

**kvsrinivasamurthy** · 09-20-2011, 05:31 AM

PL try the below code.

Sub HidingRows()

For Each ws In Worksheets
ws.Range("K9:K43").Rows.Hidden = False

For i = 9 To 43
If ws.Range("K" & i).Value = 0 Then
ws.Rows(i).EntireRow.Hidden = True
End If
Next i

Next ws

End Sub