Selection of non-empty cells

  • Thread starter Thread starter Adrian
  • Start date Start date
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

(e-mail address removed)

| Hi,
|
| How do I select all the non-empty cells in VBA code ?
| Thanks.
|
| --
| Regards,
| Adrian
|
|
 
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 :
 
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 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

(e-mail address removed)

|
| 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
|
 
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.aspx?scid=kb;en-us;104502&Product=xlw
Square Bracket Notation Is Less Efficient Than Tunneling

Of course your free to use any notation you want.

--
Regards,
Tom Ogilvy

keepITcool said:
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.
Click to expand...
Click to expand...
 
Back
Top