Good afternoon all,
I've got an insanely long code that is being used to hide/unhide rows based on a data validations field. The formulas and macros are working perfectly as it is, but as I get further into my document (which is a total of 2000 lines that will have a hide/unhide formula attacherd to them), it is beginning to take longer and longer for Excel to update (which it does every time a cell is clicked). Attached is a basic file that demonstrates what I'm doing. Code Example.xlsm
To simplify my issue, anytime a cell is clicked, it runs the below code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("$C$1") = "Red" Then
Range("A3:A4").EntireRow.Hidden = False
Else
Range("A3:A4").EntireRow.Hidden = True
End If
If Range("$C$1") = "Green" Then
Range("A5:A6").EntireRow.Hidden = False
Else
Range("A5:A6").EntireRow.Hidden = True
End If
If Range("$C$3") = "Yellow" Then
Range("A7:A8").EntireRow.Hidden = False
Else
Range("A7:A8").EntireRow.Hidden = True
End If
If Range("$C$10") = "Chevy" Then
Range("A12:A13").EntireRow.Hidden = False
Else
Range("A12:A13").EntireRow.Hidden = True
End If
If Range("$C$10") = "Ford" Then
Range("A14:A15").EntireRow.Hidden = False
Else
Range("A14:A15").EntireRow.Hidden = True
End If
If Range("$C$10") = "Toyota" Then
Range("A16:A17").EntireRow.Hidden = False
Else
Range("A16:A17").EntireRow.Hidden = True
End If
If Range("$C$19") = "PS2" Then
Range("A21:A22").EntireRow.Hidden = False
Else
Range("A21:A22").EntireRow.Hidden = True
End If
If Range("$C$19") = "PS3" Then
Range("A23:A24").EntireRow.Hidden = False
Else
Range("A23:A24").EntireRow.Hidden = True
End If
If Range("$C$19") = "Xbox" Then
Range("A25:A26").EntireRow.Hidden = False
Else
Range("A25:A26").EntireRow.Hidden = True
End If
End Sub
and anytime a cell is clicked, it runs the code again to see if anything has changed. As stated above, my issue is that running this formula every time a cell is clicked is beginning to take a lot of time, and I'm currently at line 200 out of 2000.
Is there any way to simplify the above code and make it more efficient / effective?
Bookmarks