hide a row when a cel is no

[proxis2]

hello, I'm new here .

I've searched this forum for a vba code to hide a row (or even more rows) when a cell value is no and unhide when the cell value is yes.

i found different vba codes to use a button but i want to use no buttons, it must be automated.

please could anyone help me with this??

 

[proxis2]

hello, I'm new here .

I've searched this forum for a vba code to hide a row (or even more rows) when a cell value is no and unhide when the cell value is yes.

i found different vba codes to use a button but i want to use no buttons, it must be automated.

for example, if A2 = no, then row 3 to 5 are hidden

please could anyone help me with this??

because my bad English i had to made some changes

 

[dine]

Hi,

Open This workbook and enter the below coding.

If you enter No in C1 then the rows 2 thru 5 will be hidden. If you remove no or if you enter anyother value the rows will be shown.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Range("C1").Value = "No" Then
Rows("2:5").Select
Selection.EntireRow.Hidden = True
Range("C1").Select
Else
Rows("2:5").Select
Selection.EntireRow.Hidden = False
Range("C1").Select
End If
End Sub


Hope this helps

 

[proxis2]

Hi,

Open This workbook and enter the below coding.

If you enter No in C1 then the rows 2 thru 5 will be hidden. If you remove no or if you enter anyother value the rows will be shown.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Range("C1").Value = "No" Then
Rows("2:5").Select
Selection.EntireRow.Hidden = True
Range("C1").Select
Else
Rows("2:5").Select
Selection.EntireRow.Hidden = False
Range("C1").Select
End If
End Sub


Hope this helps

yes it does,

at first row 1 also disappeared but then i found out that the cells in column b are merged, so now i unmerged the cells and it works like an charm.

thank you for helping me

 

[dine]

You are welcome!!

I am glad that it worked fine

 

[proxis2]

You are welcome!!

I am glad that it worked fine

but now i have another problem i have to solve.

what if c1 is no and row 2 to 5 are hidden and i want also that when c 6 is no, that row 7 to 9 are hidden also (etc etc)

i made some changes in the vba code but i get an error

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Range("c1").Value = "No" Then
Rows("2:5").Select
Selection.EntireRow.Hidden = True
Range("c1").Select
Else
Rows("2:5").Select
Selection.EntireRow.Hidden = False
Range("c1").Select
End If
End Sub

Private Sub Workbook_SheetChange2(ByVal Sh As Object, ByVal Target As Range)
If Range("c6").Value = "No" Then
Rows("7:9").Select
Selection.EntireRow.Hidden = True
Range("c6").Select
Else
Rows("7:9").Select
Selection.EntireRow.Hidden = False
Range("c6").Select
End If
End Sub

 

[dine]

Write the code in the same function instead of writing it in a different function.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Range("c1").Value = "No" Then
Rows("2:5").Select
Selection.EntireRow.Hidden = True
Range("c1").Select
Else
Rows("2:5").Select
Selection.EntireRow.Hidden = False
Range("c1").Select
End If

If Range("c6").Value = "No" Then
Rows("7:9").Select
Selection.EntireRow.Hidden = True
Range("c6").Select
Else
Rows("7:9").Select
Selection.EntireRow.Hidden = False
Range("c6").Select
End If

End Sub

 

[proxis2]

Write the code in the same function instead of writing it in a different function.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Range("c1").Value = "No" Then
Rows("2:5").Select
Selection.EntireRow.Hidden = True
Range("c1").Select
Else
Rows("2:5").Select
Selection.EntireRow.Hidden = False
Range("c1").Select
End If

If Range("c6").Value = "No" Then
Rows("7:9").Select
Selection.EntireRow.Hidden = True
Range("c6").Select
Else
Rows("7:9").Select
Selection.EntireRow.Hidden = False
Range("c6").Select
End If

End Sub

thank you, it works..