CODEWITHSUNDEEP
excelvba
new2vba
new2vba

Reputation: 15

Hiding all rows below cells containing certain text

I am looking to hide all rows (until row 150), that contain a certain text in a certain column. The column contains a drop down of two choices, "Yes" and "No". If the answer is yes, I want to hide all the rows below, if it is no, then not hide. ie, C2 is "No", don't hide. C3 is "Yes", hide...There are 150 rows of Yes or no, but once yes, you can hide all the rows.

I've tried to cycle the code below and it works with the first cell, but all rows after that do not work

Option Explicit

Private Sub HideRows(ByVal Target As Range)

    If Range("C2").Value = "Yes - provide details" Then
        Rows("3:150").EntireRow.Hidden = True
    ElseIf Range("C2").Value = "No" Then
        Rows("3:150").EntireRow.Hidden = False
    End If

    If Range("C3").Value = "Yes - provide details" Then
        Rows("4:150").EntireRow.Hidden = True
    ElseIf Range("C3").Value = "No" Then
        Rows("4:150").EntireRow.Hidden = False
    End If
' all the way through to C149

    If Range("C149").Value = "Yes - provide details" Then
        Rows("150").EntireRow.Hidden = True
    ElseIf Range("C149").Value = "No" Then
        Rows("150").EntireRow.Hidden = False
    End If
End Sub

I expected to be able to cycle through the first If code, but it doesn't work after the 1st set of them

Upvotes: 0

Views: 506

Answers (2)

Dude_Scott
Dude_Scott

Reputation: 641

Since it looks like you don’t want to filter because you want all rows below a “yes” to be hidden. I would recommend doing a loop. 

Option Explicit

Sub HideRows()
   Dim currRow as Integer: currRow = 1
   Dim continue as Boolean: continue = True

   While continue
      If cells(currRow,3) = "Yes - provide details" then 
         rows(currRow & ":150").EntireRow.Hidden = true
         continue = False
      Else 
          currRow = currRow + 1
      End If
    Wend
End Sub

This is untested from mobile.

Upvotes: 0

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

(this is untested)

Rows("3:150").EntireRow.Hidden = False

For i = 2 to 150
    If Range("C" & i).Value = "Yes - provide details" Then
        Rows(i + 1 & ":150").EntireRow.Hidden = True
        Exit For
    End If
Next i

Upvotes: 1

Related Questions