You have already told Harlan the Evaluate is SLOW. Using [a:a] to specify
a range is using evaluate. It is much slower than Range("A:A") or
Columns(1). Why go back to excel to evaluate an expression when VBA can do
it very well without additional overhead.

http://support.microsoft.com/default...02&Product=xlw
Square Bracket Notation Is Less Efficient Than Tunneling

Of course your free to use any notation you want.

--
Regards,
Tom Ogilvy

"keepITcool" wrote in message
...

Try like this:

note this will work only if the union will not get 'too complex'
e.g. will generate too many areas...


to get non-blank in column A:

dim rngF as range,rngC as range,rngR as range

on error resume next
set rngF = [a:a].specialcells(xlformulas)
set rngC = [a:a].specialcells(xlconstants)

if rngf is nothing then
if not rngC is nothing then
set rngR=rngC
end if
elseif rngC is nothing then
set rngR = rngF
else
set rngR = union(rngC,rngF)
endif

if not rngR is nothing then rngR.select

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Adrian wrote :

Hi,

How do I select all the non-empty cells in VBA code ?
Thanks.