CODEWITHSUNDEEP
excelvbahiderows
Kyle M
Kyle M

Reputation: 1

Hide rows based on text cell values in either of two different columns

I hide all rows with the value "Kitchen" in column 8.

I also need to hide the row if column 12 contains "No". This is an "Or" statement, not an "And" statement.

I have researched the site, but have not been able to find the answer. I would also like to speed up the process. 

Sub FOHc()

    BeginRow = 6
    EndRow = 400
    ChkCol = 8

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = "Kitchen" Or Cells(RowCnt, ChkCol).Value = Blank Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True

        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False

        End If
    Next RowCnt

End Sub

Spreadsheet

Upvotes: 0

Views: 17532

Answers (2)

SilentRevolution
SilentRevolution

Reputation: 1513

To add another logic test, simply add another Or statement.

To speed up collect all rows to hide in a range, and hide them in one go.

Also in general it's a good idea to use proper qualifiers, the statement Cells refers to what Excel considers to be the active sheet. while ThisWorkbook.Worksheets("Sheet1").Cells always refers to the sheet called "Sheet1".

Declaring all variables may prevent unwanted behavior and bugs and in general is good practice.

Here is the refactored code.

Sub FOHc()
    Dim beginRow As Long
    Dim endRow As Long
    Dim chkCol As Long
    Dim rowCnt As Long
    Dim rngResult As Range
    Dim ws As Worksheet

    beginRow = 6
    endRow = 400
    chkCol = 8

    Set ws = ThisWorkbook.Worksheets("Sheet1") 'Change this to the actual name of your sheet.

    With ws
        .Cells.EntireRow.Hidden = False 'Unhides all rows, remove line if that's not desired
        For rowCnt = beginRow To endRow
            If .Cells(rowCnt, chkCol) = "Kitchen" Or .Cells(rowCnt, chkCol) = Empty Or .Cells(rowCnt, 12) = "No" Then
                If rngResult Is Nothing Then
                    Set rngResult = .Cells(rowCnt, 1)
                Else
                    Set rngResult = Union(rngResult, .Cells(rowCnt, 1))
                End If
            End If
        Next rowCnt
    End With

    If Not rngResult Is Nothing Then rngResult.EntireRow.Hidden = True

End Sub

Upvotes: 1

Egan Wolf
Egan Wolf

Reputation: 3573

You can just add one more Or condition

If Cells(RowCnt, ChkCol).Value = "Kitchen" Or Cells(RowCnt, ChkCol).Value = Blank Or Cells(RowCnt, ChkCol2).Value = "No" Then

For speeding up macro, add Application.ScreenUpdating = False at the beginning and Application.ScreenUpdating = True at the end. This will stop updating your screen while proceeding macro.

Upvotes: 1

Related Questions