Toggle Button for Hiding/Unhiding rows

This works and is fast. Let me know if you have questions about how the code works. See attached.

Option Explicit
 
Private Sub HideUnhide_Click()
   Dim ws As Worksheet
   Dim lastC As Long
   Dim lastR As Long
   Dim scanRow As Long
   Dim scanCol As Long
   Dim HideIt As Boolean
   Dim NoNumerics As Boolean
   Dim AllBlank As Boolean
 
   For Each ws In Worksheets
 
      If Not HideUnhide Then
         ' Rows are hidden.  Unhide.
         ws.Cells.EntireRow.Hidden = False
      Else
 
         lastC = LastColumn(ws)
         lastR = LastRow(ws)
 
         If lastC <> 0 And lastR <> 0 Then
            For scanRow = 1 To lastR
 
               HideIt = True
               NoNumerics = True
               AllBlank = True
 
               For scanCol = 1 To lastC
                  If Not IsEmpty(ws.Cells(scanRow, scanCol)) Then
                     AllBlank = False
                     If IsNumeric(ws.Cells(scanRow, scanCol)) Then
                        NoNumerics = False
                        If ws.Cells(scanRow, scanCol) <> 0 Then
                           HideIt = False
                           Exit For
                        End If
                     End If
                  End If
               Next scanCol
 
               If HideIt And Not NoNumerics And Not AllBlank Then
                  ws.Cells(scanRow, 1).EntireRow.Hidden = True
               End If
 
            Next scanRow
         End If
 
      End If
   Next ws
 
End Sub
Function LastRow(ws As Worksheet) As Long
   If WorksheetFunction.CountA(ws.Cells) > 0 Then
      'Search for any entry, by searching backwards by Rows.
      LastRow = ws.Cells.Find(What:="*", After:=[A1], _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious).Row
   End If
End Function
 
Function LastColumn(ws As Worksheet) As Long
   If WorksheetFunction.CountA(ws.Cells) > 0 Then
      'Search for any entry, by searching backwards by Columns.
      LastColumn = ws.Cells.Find(What:="*", After:=[A1], _
                                 SearchOrder:=xlByColumns, _
                                 SearchDirection:=xlPrevious).Column
   End If
End Function

Originally Posted by drean03

But, do you think that it is better for only the Printed sheets will get affected? I mean the toggle button will only trigger to hide/unhide sheets with an event code. (e.g.Sheet1 and Sheet2 only, not the Sheet1(A) and Sheet2(A)). And further, this event code can be copied to future inserted worksheet/s.

I'm not exactly sure what you mean, but yes, the code can be modified to affect only certain sheets. I wrote it to affect all sheets. We can add some sort of indicator in the sheets to be affected that would be checked, or can specify explicitly which sheets would be affected. If there is a naming convention that easily identifies the sheets to be printed, that would be easiest. I would be happy to look at your full workbook if you would like (maybe too big to attach but you could email it to me, though not sure if that would be sanctioned by the mods).

Originally Posted by drean03

I am trying to figure out how to hide rows automatically when 1 row or more rows have zero (0) values and/or blank.

Not sure what you mean by that last part. My solution leaves blank lines alone since it looks like you deliberately left blank lines at the top of the sheets, and for spacing between sections.

Tip: If you can NAME your sheets in a way that indicate which should be printed, that would help in the discerning of which to apply the HIDE macro to. This would make it easier than maintaining code changes or a manual list somewhere.

So, maybe add a unique character to the sheetname that his is to be allowed. Sheet1, Sheet2, Sheet3...and you only want it to affect sheets 1 and 2, perhaps name them:

Sheet1#, Sheet2#, Sheet3

Then use this test in your macro:

For each ws in Worksheets
    If Instr(ws.Name, "#") > 0 Then

...etc

========
Another way might be to include a key cell on each sheet. If AA1 = "hide" then the macro runs on that sheet...not as elegant and easier to break if columns get deleted or such, but it's an option.

For each ws in Worksheets
    If ws.Range("AA1") = "hide" Then

...etc

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Drean, you seem to have posted the same question again. You can continue in your other thread.

Thread Closed.