Selection of non-empty cells
 

Hi,

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

--
Regards,
Adrian

Selection of non-empty cells
 

Hi Adrian

Sub test()
Dim r As Range
Set r = Union(Cells.SpecialCells(xlCellTypeConstants, 23), _
Cells.SpecialCells(xlCellTypeFormulas, 23))
r.Select
End Sub


--
XL2002
Regards

William



"Adrian" wrote in message
...
| Hi,
|
| How do I select all the non-empty cells in VBA code ?
| Thanks.
|
| --
| Regards,
| Adrian
|
|

Selection of non-empty cells
 

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.

Selection of non-empty cells
 

William,

your code will throw an error
if constants OR formulas OR both are are not found.

i've posted one with a few more checks :)

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


William wrote :

Hi Adrian

Sub test()
Dim r As Range
Set r = Union(Cells.SpecialCells(xlCellTypeConstants, 23), _
Cells.SpecialCells(xlCellTypeFormulas, 23))
r.Select
End Sub

Selection of non-empty cells
 

Hi keepITcool

Good point.

For the benefit of the OP, he should change the [a:a] in your code to
something like [a:iv] or use the used range.
--
XL2002
Regards

William



"keepITcool" wrote in message
...
|
| William,
|
| your code will throw an error
| if constants OR formulas OR both are are not found.
|
| i've posted one with a few more checks :)
|
| --
| keepITcool
| | www.XLsupport.com | keepITcool chello nl | amsterdam
|
|
| William wrote :
|
| Hi Adrian
|
| Sub test()
| Dim r As Range
| Set r = Union(Cells.SpecialCells(xlCellTypeConstants, 23), _
| Cells.SpecialCells(xlCellTypeFormulas, 23))
| r.Select
| End Sub
|

Selection of non-empty cells
 

read my post :)

is says: "to get non-blank in column A "


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


William wrote :


For the benefit of the OP, he should change the [a:a] in your code to
something like [a:iv] or use the used range.

Selection of non-empty cells
 

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.