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
    1. Dim v, ws As Worksheet
    2. Dim vRows As Variant
    3. Set ws = ThisWorkbook.Worksheets("Sheet1")
    4. ws.Activate
    5. With ws.ListObjects("Table1").DataBodyRange
    6. vRws = Filter(Application.Transpose(Evaluate(Replace(Replace("if(#=FALSE,row(#)-~,""X"")", "#", .Columns(35).Address), "~", .Rows(0).row))), "X", False)
    7. 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 "Thumbs Up" icon, below, in the bottom right corner:)

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

    Code
    1. Dim v, ws As Worksheet
    2. Dim vRws As Variant
    3. Set ws = ThisWorkbook.Worksheets("Sheet1")
    4. With ws.ListObjects("Table1").DataBodyRange
    5. vRws = Filter(Application.Transpose(ws.Evaluate(Replace(Replace("if(#=FALSE,row(#)-~,""X"")", "#", .Columns(35).Address), "~", .Rows(0).row))), "X", False)
    6. 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!