Hide/unhide rows in excel using macro

Try the below code…

Sub HideRowsBasedOnCondtion()
Dim iStart As Integer, iEnd As Long, i As Long, blHide As Variant

iStart = Sheets("First").Index
iEnd = Sheets("Last").Index

blHide = MsgBox("Do You Want To Hide The Rows?", vbYesNo, "Hide Rows?")

If blHide = vbNo Then GoTo UnhideRows

For x = iStart To iEnd
    With Sheets(x)
        For i = 11 To 28
            If .Cells(i, "I").Value = "" Then
                .Rows(i).Hidden = True
            Else
                .Rows(i).Hidden = False
            End If
        Next i
    End With
Next x

MsgBox "Rows Hidden", vbInformation, "Task Completed"
Exit Sub

UnhideRows:
    For x = iStart To iEnd
        Sheets(x).Rows("11:28").Hidden = False
    Next x
    
    MsgBox "Rows Un-Hidden", vbInformation, "Task Completed"

End Sub

Hi, aaron061883,

based on the first code maybe

Sub HideRowsBasedOnCondtion_2()
Dim iStart As Integer, iEnd As Long, i As Long, blHide As Variant
Dim x As Long

iStart = Sheets("First").Index
iEnd = Sheets("Last").Index

blHide = MsgBox("Do You Want To Hide The Rows?", vbYesNo, "Hide Rows?")

For x = iStart To iEnd
  With Sheets(x)
    .Rows("11:28").Hidden = False
    If blHide = vbYes Then
      For i = 11 To 28
        If .Cells(i, "I").Value = "" Then
          .Rows(i).Hidden = True
        End If
      Next i
    End If
  End With
Next x

End Sub

Ciao,
Holger

A for each approach with external calls via ActiveX Toggle Button.

1. Copy and paste the following (all of it) into a standard module.

Option Explicit
Public wb As Workbook
Public ws As Worksheet
Public wsFirst As Worksheet
Public wsLast As Worksheet
Public rngCell As Range

Sub AaronHide()

Set wb = ThisWorkbook
Set wsFirst = wb.Sheets("First")
Set wsLast = wb.Sheets("Last")

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    For Each ws In wb.Sheets
        If ws.Name <> "First" And ws.Name <> "Last" Then
            For Each rngCell In ws.Range("I11:I28")
                If rngCell.Value = "" Then
                    rngCell.EntireRow.Hidden = True
                End If
            Next
        End If
    Next
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Goto Reference:=Worksheets("First").Range("A1"), scroll:=True
    End With

End Sub

Sub AaronShow()

Set wb = ThisWorkbook
Set wsFirst = wb.Sheets("First")
Set wsLast = wb.Sheets("Last")

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    For Each ws In wb.Sheets
        If ws.Name <> "First" Or ws.Name <> "Last" Then
            ws.Range("I11:I28").EntireRow.Hidden = False
        End If
    Next
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Goto Reference:=Worksheets("First").Range("A1"), scroll:=True
    End With

End Sub

2. Create your button by drawing an ActiveX toggle button (Developer Tab > Controls Group > "Insert" > ToggleButton) onto the worksheet in your preferred location.

3. Right-click the toggle button > View Code.

4. Copy and Paste the following between the auto-created (by Excel) "Private Sub" and "End Sub":

If ToggleButton1.Value = True Then
        Call AaronShow
        ToggleButton1.Caption = "Hide"
    Else
        Call AaronHide
        ToggleButton1.Caption = "Show"
    End If

5. Deactivate "Design Mode" (also on the Developer Tab in the Controls Group)

Holger,

Would your code still hold in a situation where the "First" and "Last" sheets are not actually the left-most and right-most worksheets in the workbook?

#learning

Hi, AlvaroSiza,

not a big thing to alter in the code:

Sub HideRowsBasedOnCondtion_3()
Dim iStart As Integer, iEnd As Long, i As Long, blHide As Variant
Dim x As Long, lngStep As Long

iStart = Sheets("First").Index
iEnd = Sheets("Last").Index

If iEnd < iStart Then
  lngStep = -1
Else
  lngStep = 1
End If

blHide = MsgBox("Do You Want To Hide The Rows?", vbYesNo, "Hide Rows?")

For x = iStart To iEnd Step lngStep
  With Sheets(x)
    .Rows("11:28").Hidden = False
    If blHide = vbYes Then
      For i = 11 To 28
        If .Cells(i, "I").Value = "" Then
          .Rows(i).Hidden = True
        End If
      Next i
    End If
  End With
Next x

End Sub

@aaron061883:

Originally Posted by FAQ

To mark your thread solved do the following:
New Method
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Old Method
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Ciao,
Holger