Avoid Worksheet.Activate when using Filter Function

    Hello. I use the Filter Function below to filter a column in a Table and then populate a List Box with the results. I have found that I must activate the worksheet with the table in order for the function to filter correctly. But activating the worksheet causes the screen to flicker. Is there a way to avoid activating the worksheet yet have the Filter Function still filter correctly? Alternatively, can I avoid the screen flicker caused by activating the worksheet?

    Code
    Dim v, ws As Worksheet
Dim vRows As Variant
Set ws = ThisWorkbook.Worksheets("Sheet1")

ws.Activate
With ws.ListObjects("Table1").DataBodyRange

vRws = Filter(Application.Transpose(Evaluate(Replace(Replace("if(#=FALSE,row(#)-~,""X"")", "#", .Columns(35).Address), "~", .Rows(0).row))), "X", False)
v = Application.Index(.Value2, Application.Transpose(vRws), Array(1, 14, 15)


    Thanks for any assistance!

    Hello,


    Not sure to fully understand your problems ...


    Just a couple of questions :


    1. Why have you specifically selected the Worksheet_Activate() event ?


    2. Have you tested the instruction Application.ScreenUpdating ...?


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    You need to qualify the evaluate, or it's looking at the active sheet

    Code
    Dim v, ws As Worksheet
Dim vRws As Variant
Set ws = ThisWorkbook.Worksheets("Sheet1")

With ws.ListObjects("Table1").DataBodyRange

vRws = Filter(Application.Transpose(ws.Evaluate(Replace(Replace("if(#=FALSE,row(#)-~,""X"")", "#", .Columns(35).Address), "~", .Rows(0).row))), "X", False)
v = Application.Index(.Value2, Application.Transpose(vRws), Array(1, 14, 15)

    Thank you, Fluff13. This is exactly what I needed. Incorporating the worksheet object into the filter function allowed me to eliminate ws.Activate. The filter performs accurately this way and my screen flicker is gone. Many thanks!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login