Hiding Rows Based on Cell Value

Leahbeah 0
Viewed 41

I am trying to write a Macro in Excel VBA whereas any row under C2 gets hidden if the cell text contains "hide". I want the entire column to be evaluated no matter how many cells are filled (hence why LastRow is goes until the end). Don't know why this isn't working - it tells me I have a type mismatch. Can someone help pls

Sub Hide()
        Worksheets("Sheet1").Activate
            StartRow = Range("C2")
            LastRow = Range("C2", Range("C2").End(xlDown))
        For i = StartRow To LastRow
            If Cells(i, iCol).Value <> "hide" Then
                    Cells(i, iCol).EntireRow.Hidden = False
                    Else
                    Cells(i, iCol).EntireRow.Hidden = True
                    End If
                Next i
    End Sub
vba excel
1 Answers

you have some issues in your code:

  • StartRow and LastRow are not relates to Integer or Long, you implicitly defined them as Range because row property for the Range("C2") and Range("C2", Range("C2").End(xlDown)) not specified;
  • Range("C2", Range("C2").End(xlDown)) will return correct range only in case when range will not contains empty cells, otherwise it will return range from C2 to last not empty cell in column C;
  • iCol not defined in your code, so it need to be changed to something like iCol = Range("C2").Column;

your updated code below:

Sub Hide()
    Dim StartRow As Long, LastRow As Long, iCol As Long, cl As Range
    Set cl = Range("C2") 'start range
    iCol = cl.Column
    StartRow = cl.Row
    LastRow = Cells(Rows.Count, iCol).End(xlUp).Row
    For i = StartRow To LastRow
        If Cells(i, iCol).Value <> "hide" Then
            Cells(i, iCol).EntireRow.Hidden = False
        Else
            Cells(i, iCol).EntireRow.Hidden = True
        End If
    Next i
End Sub

additional variant how you can achieve desired result:

Sub Hide2()
    Dim checkRng As Range, cl As Range
    Set checkRng = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    For Each cl In checkRng
        If cl.Value <> "hide" Then
            cl.EntireRow.Hidden = False
        Else
            cl.EntireRow.Hidden = True
        End If
    Next cl
End Sub
Vasily0
Related