Row Hide

[psamu]

If I want to hide row a15 when cell value in k5=1 and also if cell value in k5=2 then hide row A16. Please help me with the vba code.

Thanks.

 

[Michael M]

Hi Psamu
You say you want to hide row "A15" when K5 =1, do you mean the entire row "A" or cell A15.

Regards
Michael M

 

[psamu]

Thanks. Yes i mean entire row "A15" when K5 =1, and When K5=2 then enire row A16 need to be hidden.

 

[psamu]

Thanks. Yes i mean entire row "A15" when K5 =1, and When K5=2 then enire row A16 need to be hidden.

and also if I want to hide A15 and A19 when value in K5=1 and When value in K5=2 Row A16 and A20 hidden. Thanks.

 

[Michael M]

What if k5=1 AND k6=2 at the same time.....does that happen ?

Or are they going to be independant of each other

Regards
Michael M

 

[psamu]

NO. Chances will be less, but if that happens then what will happen?

 

[Michael M]

psamu
This will do what you require, but I'm sure if you wait a little while there will be a number of examples of more elegant code posted to do the same thing.

Sub hidden()
    If Range("K5").Value = 1 Then
        Rows("15").hidden = True
        Rows("19").hidden = True
    End If
    If Range("K6").Value = 2 Then
        Rows("16").hidden = True
        Rows("20").hidden = True
    End If
End Sub

Regards
Michael M

 

[Scott Huish]

If you would like them to go back to being visible when they are not those values when you run the macro:

Sub test()
Rows(15).Hidden = Range("K5") = 1
Rows(19).Hidden = Range("K5") = 1
Rows(16).Hidden = Range("K6") = 2
Rows(20).Hidden = Range("K6") = 2
End Sub

If you'd like it to work automatically when you change the values, you can use a Worksheet Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Range
Set d = Intersect(Target, Range("K5:K6"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    Rows(15).Hidden = Range("K5") = 1
    Rows(19).Hidden = Range("K5") = 1
    Rows(16).Hidden = Range("K6") = 2
    Rows(20).Hidden = Range("K6") = 2
Application.EnableEvents = True
End Sub

 

[psamu]

Thanks. It is working, but I need to run the macro every time there is a change. also If the value in K5=1 and when K5 changes to 2 then what is the code. Thanks.

 

[Scott Huish]

I gave it to you. It is the worksheet change code.

Take the code you have out of the module.

Copy the Worksheet Change code I posted.

Right click on the tab for the sheet you want this to happen on.
Click on View Code
Paste into white area.
Hit Alt-q

The code will be saved with the workbook when you save the workbook.