hide/unhide rows based on cell data in a specific column

[Trent30]

If I have a formula in column A that will have either "Hide" or "Show" depending on mulitple results in other cells what would the VBA be?

I have seen multiple versions based on hiding rows based on a specific cell in the spreadsheet but I cant find it based on each row

 

[mrshl9898]

In a module or in the worksheet will be fine

 

[Trent30]

Thanks for the extra info:

Last code probably would have just hidden the ones in the top table (rows 1-9). Same as if you clicked on A1 and selected filter.

Sub HideRow()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

For i = 1 To LastRow
    If Cells(i, 1) = "Hide" Then
        Rows(i).Hidden = True
    Else
        Rows(i).Hidden = False
    End If
Next i

End Sub

Sorry, I am really a novice at this stuff so I don't completely understand how things work

 is not part of the code?
I am  assuming that "A" & "1" are related to the column?
"i" stands for instance?
"sht" stands for Sheet?

 

[mrshl9898]

Hope this helps

Sub HideRow()

Dim sht As Worksheet
Dim LastRow As Long

''sht is a short name for the active sheet
Set sht = ActiveSheet

''this is finding the last row on the sheet in column A
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

''this is starting at i 1 and going up to the last row (i will basically represent the row number)
For i = 1 To LastRow
    If Cells(i, 1) = "Hide" Then ''cells(1,1) is cell A1, it will go to cells(2,1) next which is B1
        Rows(i).Hidden = True ''Hides the row it is up to
    Else
        Rows(i).Hidden = False ''if Hide is not found it will unhide (or not hide) the row
    End If
Next i ''changes the i variable to the next number

End Sub

 

[Trent30]

Hope this helps

Sub HideRow()

Dim sht As Worksheet
Dim LastRow As Long

''sht is a short name for the active sheet
Set sht = ActiveSheet

''this is finding the last row on the sheet in column A
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

''this is starting at i 1 and going up to the last row (i will basically represent the row number)
For i = 1 To LastRow
    If Cells(i, 1) = "Hide" Then ''cells(1,1) is cell A1, it will go to cells(2,1) next which is B1
        Rows(i).Hidden = True ''Hides the row it is up to
    Else
        Rows(i).Hidden = False ''if Hide is not found it will unhide (or not hide) the row
    End If
Next i ''changes the i variable to the next number

End Sub

Great I think I understand now, thank you for going through for me.

If I want it to automatically change to hide the cell rather than having to manually run the macro would this be different?

 

[mrshl9898]

Add this to the worksheet module if you want it to run when a value changes in column A

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Column = 1 Then
    Application.Run ("HideRow")
    End If

End Sub

 

[Trent30]

Add this to the worksheet module if you want it to run when a value changes in column A

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Column = 1 Then
    Application.Run ("HideRow")
    End If

End Sub

So does that mean the code will look like this?

Sub HideRow()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

For i = 1 To LastRow
If Cells(i, 1) = "Hide" Then
Rows(i).Hidden = True
Else
Rows(i).Hidden = False
End If
Next i

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)



If Target.Column = 1 Then

Application.Run ("HideRow")

End If



End Sub

 

[mrshl9898]

That should work, just make sure it's in the sheet module.