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:

Please Login or Register  to view this content.

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..

Please Login or Register  to view this content.

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

**TMS** · 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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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!?...

**TMS** · 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.

Please Login or Register  to view this content.

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