I am trying to create a Toggle Button that will hide rows that are unused on two worksheets in a workbook.  I have gotten the code to work on one worksheet mostly.  Due to formatting I was checking each range which is basically a separate table and then hiding that row.  However cells A46:A62 are tied to cells A6:A22 with simple A46=A6 formulas and these rows never hide.  Below is the code for the toggle that I am using:

Private Sub ToggleButton1_Click()

    If ToggleButton1 Then
   
        Dim r As Range, c As Range
        Set r = Range("A6:A22")
   
    Application.ScreenUpdating = False

    For Each cell In Range("a6:a22")
        cell.EntireRow.Hidden = cell.Value = ""
    Next cell

    For Each cell In Range("a28:a40")
        cell.EntireRow.Hidden = cell.Value = ""
    Next cell
   
    For Each cell In Range("a46:a62")
        cell.EntireRow.Hidden = cell.Value = ""
    Next cell
       
    For Each cell In Range("a66:a100")
        cell.EntireRow.Hidden = cell.Value = ""
    Next cell

    Else

        Rows("6:100").EntireRow.Hidden = False
   
    Application.ScreenUpdating = True

    End If

End Sub

I would also like to make this one toggle located on Sheet1 hide the same rows on Sheet2 if that is possible.

 

Re:  hiding rows in two sheets

This seems to work.  I tried to stay close to your original code setup.
Note:  Use   Option Explicit   as the first line in all code modules.

'---
Private Sub ToggleButton1_Click()
  Dim r As Range, cell As Range
  Dim vArr As Variant
  Dim N As Long

  vArr = Array("Sheet1", "Sheet2")
  Application.ScreenUpdating = False
 
  For N = LBound(vArr) To UBound(vArr)
    Set r = Sheets(vArr(N)).Range("A1:A100").Cells
    If ToggleButton1 Then
     
      For Each cell In r.Range("a6:a22")
         cell.EntireRow.Hidden = cell.Value = ""
      Next cell
 
      For Each cell In r.Range("a28:a40")
         cell.EntireRow.Hidden = cell.Value = ""
      Next cell
     
      For Each cell In r.Range("a46:a62")
         cell.EntireRow.Hidden = cell.Value = ""
      Next cell
         
      For Each cell In r.Range("a66:a100")
         cell.EntireRow.Hidden = cell.Value = ""
      Next cell
    Else
      r.Rows("6:100").EntireRow.Hidden = False
    End If
  Next 'N
  Application.ScreenUpdating = True
  Set r = Nothing
End Sub

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2

Thanks that helps. I was messing up the array somewhere when I was attempting it. The code does function across both pages effectively except for the rows with the A Cells that are linked to other cells such as A46=A6 and some on Sheet2. 

Hi James,

This is the forum to discuss questions and feedback for Microsoft Excel, your issue is related to Office DEV, I recommend you post the question to the MSDN forum for Excel

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

George Zhao
TechNet Community Support