CODEWITHSUNDEEP
vbaexcel
J. Doe
J. Doe

Reputation: 45

How to hide a series of rows in Excel until the value changes

So first of all i have a macro in my excel workbook that Hides rows based on a cell value. And this works. First of all here's what my worksheet looks like:

Screenshot of my worksheet

Screenshot of my worksheet

And here is my code:

Option Explicit
Sub Hide()
Application.ScreenUpdating = False

Dim wks As Worksheet
Dim Lastrow As String
Dim Rng As Range
Dim cell As Range

On Error Resume Next
For Each wks In ThisWorkbook.Worksheets
    
    With wks
     wks.Select
      Rows.Hidden = False
      Lastrow = Range("H" & Rows.Count).End(xlUp).Row          '
               Set Rng = Range("H1:H" & Lastrow) '
               For Each cell In Rng
                    If cell.Value = "Closed" Then  '
                       cell.EntireRow.Hidden = True
                    End If
                Next cell
    End With
    Exit For
Next wks

MsgBox "All Rows containing Closed in Column H have been hidden", vbInformation, "Information"

Application.ScreenUpdating = True
End Sub

So this will hide any row that contains "Closed" on my first worksheet in the column "H".

But as you can see on my print screen, i would like to hide all the rows between the first met value "closed" and the second value "closed". And do this until the last row. But i don't know how to do this, can someone help me please?

Upvotes: 4

Views: 1093

Answers (2)

Dirk Reichel
Dirk Reichel

Reputation: 7979

To do it in a fast way, try this:

Option Explicit
Sub Hide()
  Application.ScreenUpdating = False
  Dim wks As Worksheet
  Dim Lastrow As String
  Dim Rng As Range, i As Long
  Dim cell As Variant
  For Each wks In ThisWorkbook.Worksheets
    wks.Rows.Hidden = False
    Lastrow = wks.Range("H" & Rows.Count).End(xlUp).Row
    If Lastrow > 1 Then
      cell = wks.Range("H1:H" & Lastrow).Value
      i = 1: Set Rng = Nothing
      While i <= Lastrow
        For i = i To Lastrow
          If LCase(cell(i, 1)) = "closed" Then Exit For
        Next
        For i = i To Lastrow
          If LCase(cell(i, 1)) = "closed" Or cell(i, 1) = "" Then
            If Rng Is Nothing Then
              Set Rng = wks.Rows(i)
            Else
              Set Rng = Union(Rng, wks.Rows(i))
            End If
          Else
            Exit For
          End If
        Next
      Wend
      If Not Rng Is Nothing Then Rng.EntireRow.Hidden = True
    End If
  Next
  MsgBox "All Rows containing Closed in Column H have been hidden", vbInformation, "Information"
  Application.ScreenUpdating = True
End Sub

To do it without while and have easier access for conditions:

Option Explicit
Sub Hide()
  Application.ScreenUpdating = False
  Dim wks As Worksheet
  Dim Lastrow As String
  Dim i As Long, hideB As Boolean
  Dim cell As Variant
  For Each wks In ThisWorkbook.Worksheets
    wks.Rows.Hidden = False
    Lastrow = wks.Range("H" & Rows.Count).End(xlUp).Row
    If Lastrow > 1 Then
      cell = wks.Range("H1:H" & Lastrow).Value
      Set Rng = Nothing: hideB = False
      For i = 1 To Lastrow
        If Len(cell(i, 1)) Then
          'this determinates if the line will be hidden
          hideB = (LCase(cell(i, 1)) = "closed")
          'if you want to check G also => hideB = (LCase(cell(i, 1)) = "closed") And (LCase(wks.cells(i, "G")) = "keyword2")
        End If
        If hideB Then
          If Rng Is Nothing Then
            Set Rng = wks.Rows(i)
          Else
            Set Rng = Union(Rng, wks.Rows(i))
          End If
        End If
      Next
      If Not Rng Is Nothing Then Rng.EntireRow.Hidden = True
    End If
  Next
  MsgBox "All Rows containing Closed in Column H have been hidden", vbInformation, "Information"
End Sub

Upvotes: 1

Bob Phillips
Bob Phillips

Reputation: 437

Does this do what you want?

Sub Hide()
Dim wks As Worksheet
Dim lastrow As Long
Dim startrow As Long
Dim i As Long

    Application.ScreenUpdating = False

    On Error Resume Next
    For Each wks In ThisWorkbook.Worksheets

        With wks

            .Rows.Hidden = False
            lastrow = .Range("H" & .Rows.Count).End(xlUp).Row
            For i = 2 To lastrow

                If .Cells(i, "H").Value = "Closed" Then

                    startrow = i + 1
                    Do Until .Cells(i + 1, "H").Value = "Closed" Or i + 1 > lastrow

                        i = i + 1
                    Loop
                    If .Cells(i + 1, "H").Value = "Closed" Then

                        If i >= startrow Then .Rows(startrow).Resize(i - startrow + 1).Hidden = True
                        i = i + 1
                    End If
                End If
            Next i
        End With
    Next wks

    MsgBox "All Rows containing Closed in Column H have been hidden", vbInformation, "Information"

    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Related Questions