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 "Thumbs Up" 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!