hide a row when a cel is no

proxis2

New Member
Joined
Feb 17, 2010
Messages
5
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??
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

proxis2

New Member
Joined
Feb 17, 2010
Messages
5
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

Board Regular
Joined
Feb 16, 2010
Messages
59
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.

D:\Documents and Settings\dinesh.kumar.kannan\My Documents\My Pictures\hide and show rows.bmp


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

New Member
Joined
Feb 17, 2010
Messages
5
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.

D:%5CDocuments%20and%20Settings%5Cdinesh.kumar.kannan%5CMy%20Documents%5CMy%20Pictures%5Chide%20and%20show%20rows.bmp


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

Board Regular
Joined
Feb 16, 2010
Messages
59

ADVERTISEMENT

You are welcome!!

I am glad that it worked fine :)
 

proxis2

New Member
Joined
Feb 17, 2010
Messages
5
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
 
Last edited:

dine

Board Regular
Joined
Feb 16, 2010
Messages
59
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

New Member
Joined
Feb 17, 2010
Messages
5
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..
 

Watch MrExcel Video

Forum statistics

Threads
1,123,517
Messages
5,602,120
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top