Doesn't a multiple cell range solve your problem?

"LuisE" wrote:

Thanks, Joel
My problem is not defining the range as a variable or making it to auto
expand;
it is querying the content of every single cell in the range.



"Joel" wrote:

You can set a range variable
with sheets("sheet2")
LastColumn = .cells(1,Columns.Count).end(xltoleft).column
LastRow = .cells(Rows.count,"A").end(xlup).row
set QRange = .range(.cells(1,"A"),.cells(LastRow,LastColumn))
MyTable.MyField=QRange
end with

"LuisE" wrote:


I need to query several records (averaging 2,000) that change every week.
First of all Microsoft query tells me that the SQL statement is too long.
Let's say that I reduce the size of the statement to a workable size if not
other options exist,

What statement can I use to refer to every single element of a dynamic range
in the WHERE part of the SQL statement instead of typing every single one
AND, AND ?

I tried the following but obviously didnt work because it reads it as a
single item
"WHERE (MyTable.MyField= ' & Sheet2.Range("A1").CurrentRegion & "')"