VBA Code - I think too much

[Grammarjunkie]

Whenever a dropdown is used on my spreadsheet, or whenever any information is input, the whole sheet shakes. I assume that's because it's checking all the VBA If statements.

If that is the case, would it make it more seamless if all these were just one code? If so, would someone mind putting it into one code? I know that's a tedious task, but I'd really appreciate it. Right now, the way it is, I'm not sure it would be usable with it shaking every time data is entered.

If having it in one code also wouldn't help, that information is helpful, too. Then I can decide to deal with the shaking or just remove it all and never have rows hidden.

Thanks for any advice/help. The following are the VBA codes I'm using




If Range("J3") = "TPO" Then
Rows("14:15").Hidden = False
Else
Rows("14:15").Hidden = True
End If

If (Range("D6") <> "") And (Range("E18") <> "") And (Range("D5") <> Range("E17")) Then
Rows("39:41").Hidden = False
Else
Rows("39:41").Hidden = True
End If

If Range("F45") = "N" Then
Rows("46:48").Hidden = True
Else
Rows("46:48").Hidden = False
End If

If Range("E19") = "" Then
Rows("49:52").Hidden = True
Else
Rows("49:52").Hidden = False
End If

If Range("D13") = "Purchase" Then
Rows("59").Hidden = False
Else
Rows("59").Hidden = True
End If

If Range("E22") = "" Then
Rows("53:56").Hidden = True
Else
Rows("53:56").Hidden = False
End If

If Range("D13") = "Purchase" Then
Rows("104:113").Hidden = False
Else
Rows("104:113").Hidden = True
End If

If Range("J3") = "TPO" Then
Rows("118:121").Hidden = False
Else
Rows("118:121").Hidden = True
End If

If Range("D13") = "Purchase" Then
Rows("127:130").Hidden = False
Else
Rows("127:130").Hidden = True
End If

If Range("J3") = "TPO" Then
Rows("126").Hidden = False
Else
Rows("126").Hidden = True
End If

If Range("F95") = "Y" Then
Rows("96:97").Hidden = False
Else
Rows("96:97").Hidden = True
End If

If Range("F89") = "Y" Then
Rows("90:95").Hidden = False
Else
Rows("90:95").Hidden = True
End If

If Range("F75") = "Y" Then
Rows("76").Hidden = False
Else
Rows("76").Hidden = True
End If

If Range("F75") = "N" Then
Rows("77:80").Hidden = False
Else
Rows("77:80").Hidden = True
End If

 

[My Aswer Is This]

Try adding this line of code to the beginning of your script:

Application.ScreenUpdating = False

And this line of code to the end of your script:

Application.ScreenUpdating = True

 

[Grammarjunkie]

Try adding this line of code to the beginning of your script:

Application.ScreenUpdating = False

And this line of code to the end of your script:

Application.ScreenUpdating = True

Haha, well that was easy! Sorry. VBA is completely unexplored territory for me. I really appreciate you replying so fast!

 

[My Aswer Is This]

In case you would like to know.
You can write your code like this:

If [J3] = "TPO" Then Rows("14:15").EntireRow.Hidden = False Else Rows("14:15").EntireRow.Hidden = True


If [D6] <> "" And [E18] <> "" And [D5] <> [E17] Then Rows("39:41").Hidden = False Else Rows("39:41").Hidden = True

 

[Rick Rothstein]

In case you would like to know.
You can write your code like this:

If [J3] = "TPO" Then Rows("14:15").EntireRow.Hidden = False Else Rows("14:15").EntireRow.Hidden = True


If [D6] <> "" And [E18] <> "" And [D5] <> [E17] Then Rows("39:41").Hidden = False Else Rows("39:41").Hidden = True

To follow up on the comment by My Aswer Is This, all of the code posted in Message #1 can be replaced by these five lines of code...

[14:15,118:121,126:126].EntireRow.Hidden = [J3<>"TPO"]
[59:59,104:113].EntireRow.Hidden = [D13<>"Purchase"]
[39:41].EntireRow.Hidden = [OR(D6="",E18="",D5=E17)]
[42:52].EntireRow.Hidden = [E19=""]
[46:48].EntireRow.Hidden = [F45="N"]