Simplify my macro trigger so my table stops flickering?

[Jeevz_87]

Hi All,


I'm looking to simplify my macro which is triggered when a value within a drop down changes. The macro then finds all rows within a table that do not contain that value that matches the drop down and calls a macro to hide rows that do not match. As this process contains a few steps in it, it tends to cause the table to flicker momentarily whilst it filters the table view and I can assume that it has to do with the length of the macro process. My question is, it there any way of simplifying the macro below so that there's fewer steps and hopefully less 'flicker' when the macro is triggered?

Its a superficial thing to ask but I know the users will wonder if their laptops are having a melt-down for some reason.

Here's my code

Private Sub worksheet_change(ByVal target As Range)
Set target = Range("A2")
If target.Value = "Location 1" Then
 Call HideRows
End If
If target.Value = "Location 2" Then
Call HideRows
End If
If target.Value = "Location 3" Then
Call HideRows
End If
If target.Value = "Location 4" Then
Call HideRows
End If
If target.Value = "Location 5" Then
Call HideRows
End If
If target.Value = "Location 6" Then
Call HideRows
End If
If target.Value = "Location 7" Then
'The macro call out below hides all rows in a hidden helper column in the table which have the value of '0'.
Call HideRows
End If
End Sub

To clarify, I want a way to simplify the above process so that it doesnt take as long to execute the filtering of the data within a table and hopefully reduce the flickering that occurs when the macro is triggered and executing the action.

Many thanks,


Jeevz

 

[Kerryx]

To stop the flickering use

Sub subname()
Application.ScreenUpdating = False

' Code you want to run
Application.ScreenUpdating = True
End Sub

 

[footoo]

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, [A2]) Is Nothing Then
    Set target = [A2]
Else: Exit Sub
End If
Application.EnableEvents = False
If Left(target, 9) = "Location " & Right(target, 1) < 8 Then Call HideRows
Application.EnableEvents = True
End Sub

 

[footoo]

Revised :

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, [A2]) Is Nothing Then
    Set target = [A2]
Else: Exit Sub
End If
Application.EnableEvents = False
If Left(target, 9) = "Location " And Right(target, 2) < 8 Then Call HideRows
Application.EnableEvents = True
End Sub

 

[Fluff]

As Target is the cell that has changed you should never try to set it to another range.

Private Sub worksheet_change(ByVal Target As Range)
If Target.Address(0, 0) = "A2" Then
   Application.ScreenUpdating = False
   If Target.Value Like "Location*" And Right(Target.Value, 2) < 8 Then Call HideRows
End If
End Sub

 

[footoo]

As Target is the cell that has changed you should never try to set it to another range.

Private Sub worksheet_change(ByVal Target As Range)
If Target.Address(0, 0) = "A2" Then
   Application.ScreenUpdating = True
   If Target.Value Like "Location*" And Right(Target.Value, 2) < 8 Then Call HideRows
End If
End Sub

Except if the range being changed might consist of more cells than A2.
Also : Application.ScreenUpdating = True ?

Private Sub worksheet_change(ByVal target As Range)
Dim r As Range
If Not Intersect(target, [A2]) Is Nothing Then
    Set r = [A2]
Else: Exit Sub
End If
Application.ScreenUpdating = False
If Left(r, 9) = "Location " And Right(r, 2) < 8 Then Call HideRows
End Sub

 

[Jeevz_87]

Hi Everyone,

Thank you for your responses to this question. In fairness all the solutions here actually worked (I ran every one of the till i settled at one I preferred in this case) and the macro from @Kerryx did also stop the flickering.

Thank you for your help @footoo and @Fluff - One day I'll be as proficient as you

The issue I have is more to do with reading VBA as its different to reading formulae, dax and python code. Would you recommend any books that can at least help me better comprehend the VBA that I use?

Many thanks again everyone, apologies again for letting this dangle as long as I did.

Jeevz

 

[Fluff]

Glad we could help & thanks for the feedback.

I cannot recommend any books on VBA, as I have never read any of them.