VBA to set a Range with a sql type statement (Select * From tbl1 Where tbl1[column_Color]="red") RRS feed

  • Question

  • Dear all, 

    for some VBA coding I need to select a "sub-range" from a table where a specific column has a specific value.

    as example lets use the following:

    Select * From tbl1 Where tbl1[column_Color]="red"

    If I use the followings:

    Dim resultRange as Range

    Set resultRange = Select * From tbl1 Where tbl1[column_Color]="red"

    as far as I am concern it will never work ;) what is the best way to do this? I do not want to use "for each row in bla bla bla"...

    many thanks in advance!


    Tuesday, January 20, 2015 7:35 PM


All replies

  • Try recording a macro of setting a filter on the relevant column of the table.

    Regards, Hans Vogelaar (

    Tuesday, January 20, 2015 8:13 PM
  • Hi,

    thanks for your answer, 

    I was trying to manipulate the table "only via code".... I don't want the users of my file look at a screen and see filtering and un-filtering and other things...

    Other suggestions?

    many thanks


    Wednesday, January 21, 2015 5:47 AM
  • You could add

    Application.ScreenUpdating = False

    at the beginning of the macro, and

    Application.ScreenUpdating = True

    at the end.

    Otherwise, you'll have to loop through the rows.

    Regards, Hans Vogelaar (

    Wednesday, January 21, 2015 7:01 AM