Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Using code on multiple worksheets in a workbook

  Asked By: Leona    Date: Dec 07    Category: MS Office    Views: 953
  

I have the following code that I use on a worksheet but I now wish to
use this code on all worksheets in the workbook - can someone help with
the code for this.

Sub hide()
Dim rw
For Each rw In ActiveSheet.UsedRange.Rows
If rw.Cells(3) = 0 And rw.Cells(5) = 0 And rw.Cells(6) = 0 Then
rw.Hidden = True
Next rw
End Sub

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Velma Adams     Answered On: Dec 07

This code  should work on whichever sheet is the active sheet, so it
should run on all of them. Unless what you want is for all the sheets
to be processed in one run of the macro? In which case (untested):

Sub hide()
For each ws in activeworkbook.sheets
Dim rw
For Each rw In ws.UsedRange.Rows
If rw.Cells(3) = 0 And rw.Cells(5) = 0 And rw.Cells(6) = 0 Then
rw.Hidden = True
Next rw
next ws
End Sub

 
Answer #2    Answered By: Wilbur Hall     Answered On: Dec 07

It needed a Dim statement for the ws which I put in before the first
line of code, but when I ran the code  it got stuck on the first sheet
and was looping continuously. The debug highlighted "Next rw" and
yet this worked ok when I was only running my code on a single sheet.

Yes I did mean that I wanted the code to start with the first sheet
and work through every sheet in the workbook.

Could you help  a little more with this.

 
Answer #3    Answered By: Chione Massri     Answered On: Dec 07

This works(tested):

Sub hide()
Dim ws
Dim rw
For Each ws In ActiveWorkbook.Sheets
For Each rw In ws.UsedRange.Rows
If rw.Cells(3) = 0 And rw.Cells(5) = 0 _
And rw.Cells(6) = 0 Then rw.Hidden = True
Next rw
Next ws
End Sub

 
Didn't find what you were looking for? Find more on Using code on multiple worksheets in a workbook Or get search suggestion and latest updates.




Tagged: