Hide rows in VBA based on cell values

[thedeadzeds]

Guys,

Is this at all possible in VBA based on worksheet called 'Sales'

if F6 = VW and I6 = Bristol, hide rows 64 to 65
if F6 = VW and I6 = Bath, hide rows 68 to 70 and 81 to 85
if F6 = VW and I6 is empty, hide rows 64 to 65 and 89 to 90
if F6 is empty and I6 is empty, show rows 64 to 100
Else show all rows

 

[RMS123]

This should work

Sub hide_rows()


If Range("F6") = "VW" And Range("I6") = "Bristol" Then


    Rows("64:65").Hidden = True
    




ElseIf Range("F6") = "VW" And Range("I6") = "Bath" Then


    Rows("68:70").Hidden = True
    Rows("81:85").Hidden = True




ElseIf Range("F6") = "VW" And Range("I6") = "" Then


    Rows("64:65").Hidden = True
    Rows("89:90").Hidden = True




ElseIf Range("F6") = "" And Range("I6") = "" Then


    Rows("64:90").Hidden = True
    
Else: Rows("64:90").Hidden = False


End If


End Sub

 

[thedeadzeds]

Thanks for this, I have changed this to run based on workbook change. This works great but both cells I6 and F6 are validation drop downs and I have noticed the marco will only run once I click off the cells. Is there away around this to make this run as soon as the validation box is changed? Hope this makes sense, code is below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Range("F6") = "VW" And Range("I6") = "Bristol" Then


    Rows("100:120").Hidden = True
    
ElseIf Range("F6") = "VW" And Range("I6") = "Bath" Then




    Rows("121:140").Hidden = True
    Rows("142:142").Hidden = True




ElseIf Range("F6") = "VW" And Range("I6") = "" Then


    Rows("143:143").Hidden = True
    Rows("146:146").Hidden = True


ElseIf Range("F6") = "" And Range("I6") = "" Then


    Rows("170:180").Hidden = True
    
Else: Rows("100:181").Hidden = False


End If


End Sub