Hide entire row based on a value in a cell (in that row)

[sdoppke]

Hi everyone, i did some searching and was able to write this script, but it does not work. Anyone have any ideas?

Sub HideRows()
Dim cell As Range
Application.ScreenUpdating = False
ActiveSheet.Rows.Hidden = False
For Each cell In Range("CK:CK")
If cell.Value = "P" Then
cell.EntireRow.Hidden = True
End If
Next
For Each cell In Range("CK:CK")
If cell.Value = "O" Then
cell.EntireRow.Hidden = True
End If
Next
Rows("240:1197").Select
    Selection.EntireRow.Hidden = True
    Rows("3:239").Select
    Selection.EntireRow.Hidden = False
    Sheets("Schedule Tool").Range("A1") = "Now Showing: Week 1"
    Sheets("Schedule Tool").Range("A2") = Sheets("Labor Budget").Range("B1")
    Range("F5").Select
Application.ScreenUpdating = False
End Sub

Thanks a lot for any input.

sd

 

[Joe4]

Try changing your last line to:

Application.ScreenUpdating = True

Also, here are a few improvements you can make to the beginning of your code.

You really don't want to loop through each and every cell on your worksheet. Find the last row in column CK and stop there.
Also, no need to go through that first loop twice, checking for "P" or "O". Do it all in one loop.

The first part of your code can be re-written like this:

    Dim cell As Range
    Dim myLastRow As Long
 
    Application.ScreenUpdating = False
 
    ActiveSheet.Rows.Hidden = False
 
    myLastRow = Cells(Rows.Count, "CK").End(xlUp).Row
    For Each cell In Range("CK1:CK" & myLastRow)
        If (cell.Value = "P") Or (cell.Value = "O") Then
            cell.EntireRow.Hidden = True
        End If
    Next
...

I tested it out, and it does successfully hide all rows with O and P in column CK.

 

[sdoppke]

Try changing your last line to:

Application.ScreenUpdating = True

Also, here are a few improvements you can make to the beginning of your code.

You really don't want to loop through each and every cell on your worksheet. Find the last row in column CK and stop there.
Also, no need to go through that first loop twice, checking for "P" or "O". Do it all in one loop.

The first part of your code can be re-written like this:

    Dim cell As Range
    Dim myLastRow As Long
 
    Application.ScreenUpdating = False
 
    ActiveSheet.Rows.Hidden = False
 
    myLastRow = Cells(Rows.Count, "CK").End(xlUp).Row
    For Each cell In Range("CK1:CK" & myLastRow)
        If (cell.Value = "P") Or (cell.Value = "O") Then
            cell.EntireRow.Hidden = True
        End If
    Next
...

I tested it out, and it does successfully hide all rows with O and P in column CK.

Joe, thanks a ton for the reply. I tried this script (the improvemets) and fixed the false to true.

But it doesnt seem to hide anything. Can you think of what im doing wrong?

sd

 

[sdoppke]

Joe, thanks a ton for the reply. I tried this script (the improvemets) and fixed the false to true.

But it doesnt seem to hide anything. Can you think of what im doing wrong?

sd

Here is the actual code:

Sub HideRows()
Dim cell As Range
    Dim myLastRow As Long
    Application.ScreenUpdating = False
    ActiveSheet.Rows.Hidden = False
    myLastRow = Cells(Rows.Count, "CK").End(xlUp).row
    For Each cell In Range("CK5:CK" & myLastRow)
        If (cell.Value = "P") Or (cell.Value = "O") Then
            cell.EntireRow.Hidden = True
        End If
    Next
Rows("240:1197").Select
    Selection.EntireRow.Hidden = True
    Rows("3:239").Select
    Selection.EntireRow.Hidden = False
    Sheets("Schedule Tool").Range("A1") = "Now Showing: Week 1"
    Sheets("Schedule Tool").Range("A2") = Sheets("Labor Budget").Range("B1")
    Range("F5").Select
Application.ScreenUpdating = True
End Sub

 

[sdoppke]

Here is the actual code:

Sub HideRows()
Dim cell As Range
    Dim myLastRow As Long
    Application.ScreenUpdating = False
    ActiveSheet.Rows.Hidden = False
    myLastRow = Cells(Rows.Count, "CK").End(xlUp).row
    For Each cell In Range("CK5:CK" & myLastRow)
        If (cell.Value = "P") Or (cell.Value = "O") Then
            cell.EntireRow.Hidden = True
        End If
    Next
Rows("240:1197").Select
    Selection.EntireRow.Hidden = True
    Rows("3:239").Select
    Selection.EntireRow.Hidden = False
    Sheets("Schedule Tool").Range("A1") = "Now Showing: Week 1"
    Sheets("Schedule Tool").Range("A2") = Sheets("Labor Budget").Range("B1")
    Range("F5").Select
Application.ScreenUpdating = True
End Sub

I figured it out, the last part, is simply undoing the first part!! dugh!!

Thanks again for the help.

sd

 

[Joe4]

I figured it out, the last part, is simply undoing the first part!! dugh!!

I was just about to ask you that, to make sure you aren't looking in rows 3:239 that you are unhiding at the end.