Automatically hiding and unhiding rows based on a cell value

[Davio]

I've researched most places on the internet for a solution and have been unsuccessful. I have VERY limited knowledge regarding macros but my spreadsheet requires automatic updates. I need to figure out how to have a given row hidden if column H of the same row is equal to "N". Any help is appreciated.

 

[Smitty]

Where did you put the code? It should go in the worksheet specific module, not a general module. You can get there quickly by right-clicking on the sheet tab and selecting View Code.

 

[Davio]

It deletes the information but does not hide the row. Should both codes go in the worksheet?

 

[Smitty]

How about posting the code that you've got? AutoFilter won't delete anything.

 

[Davio]

This is the autofilter code.

Sub AutoFilter_in_Excel()
 Range("A3").AutoFilter Field:=8, Criteria1:="Y"
 End Sub
Private Sub Worksheet_Calculate()
 ' AutoFilter code here
 End Sub

I think I'll go back to the original code. I'm not a big fan or the "table" look on the summary sheet.

Sub HURows()

    BeginRow = 3<o:p></o:p>
    EndRow = 105<o:p></o:p>
    ChkCol = 8<o:p></o:p>
    For RowCnt = BeginRow ToEndRow<o:p></o:p>
        If Cells(RowCnt,ChkCol).Value = " " Then<o:p></o:p>
            Cells(RowCnt,ChkCol).EntireRow.Hidden = True<o:p></o:p>
        Else<o:p></o:p>
            Cells(RowCnt,ChkCol).EntireRow.Hidden = False<o:p></o:p>
        End If<o:p></o:p>
    Next RowCnt<o:p></o:p>
End Sub 

 

[Davio]

The original code works but it does not automatically hide the rows. Is there a "loop" code?

 

[Smitty]

Private Sub Worksheet_Calculate()
    With Range("A3")
        .AutoFilter
        .AutoFilter Field:=8, Criteria1:="Y"
    End With
End Sub

For the other code you'd need to change the criteria:

If Cells(RowCnt,ChkCol).Value = " " Then

 

[Davio]

I've edited that portion but it still does not auto update. I eventually have to go to that page and manually run the macro for the page to be updated. Is there any code to add to it to make it run automatically or continuously?

 

[Smitty]

I just tested and this works for me:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
        With Range("A3")
            .AutoFilter
            .AutoFilter Field:=8, Criteria1:="Y"
        End With
    Application.EnableEvents = True
End Sub

 

[Davio]

It works great, but is there a way to have the same effect without the filter symbols visible?

 

[Smitty]

Not that I can think of without a loop.