Macro to hide rows that contain a certain value

[mrdanielatkins]

Hello All,

I'm diving deeper into Excel than I ever have and honestly, I'm in over my head. I need to write a VBA code that will hide any Row (A20:A80) that contains "Hide this Row" in column "N". I just need this function to work on this one worksheet, but I need it to update as the values update in the column as they may change.

Any help would be greatly a

ppreciated.

[/URL]via Imgflip Meme Generator[/IMG]

 

[mrdanielatkins]

Sorry, I meant Row 20:80.

 

[billandrew]

Might be a better way, however this should work.

Sub HideThisRow()    For i = 20 To 80
    If Range("N" & i) = "Hide This Row" Then Rows(i).EntireRow.Hidden = True
    Next
    End Sub

 

[mrdanielatkins]

Nothing happened. I've done very little VBA, so it is probably user error. I just copied and pasted this in the VBA dialog box for the workbook, correct?

 

[Joe4]

but I need it to update as the values update in the column as they may change.

How are the values changing?
Is someone manually updating the values in column N?
If not, what is in column N (if a formula, please post it), and what is driving the changes?

 

[mrdanielatkins]

N20:N80 are pulling from a set of buttons on the other worksheets. Here is the code:

=IF('CO# 01'!$I$49=3,"Hide this Row","")

 

[Joe4]

So, how exactly is column I on your 'CO# 1' sheet being updated?
And do the row number between the two sheets correspond (i.e. does updating column I in row 49 update column N on row 49 of your other sheet)?

 

[mrdanielatkins]

Each one of the buttons in the Red Rectangle trigger a different value in I-49. There are quite a few references to this cell on each sheet. To answer you other question, No, the row number between the two sheets does not correspond.

[/URL]

 

[Joe4]

OK, try this.
Go to the sheet with the values in column N where you want to hide the rows, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the resulting VB Editor window:

Private Sub Worksheet_Calculate()
    Dim cell As Range
    For Each cell In Range("N20:N80")
        If UCase(cell.Value) = "HIDE THIS ROW" Then
            Rows(cell.Row).Hidden = True
        Else
            Rows(cell.Row).Hidden = False
        End If
    Next cell
End Sub

This should automatically hide/unhide those rows as the values in column N change.

 

[mrdanielatkins]

Thanks for all of your help. I tried the code, it is definitely trying to do something. Ultimately, I'm getting a runtime Error referencing the highlighted line below, then Excel crashes:

Private Sub Worksheet_Calculate()
Dim cell As Range
For Each cell In Range("N20:N80")
If UCase(cell.Value) = "HIDE THIS ROW" Then
Rows(cell.Row).Hidden = True
Else
Rows(cell.Row).Hidden = False
End If
Next cell
End Sub

[/URL]