Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection of non-empty cells
Hi,
How do I select all the non-empty cells in VBA code ? Thanks. -- Regards, Adrian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Randomly populating empty cells with other text cells | Excel Discussion (Misc queries) | |||
Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
When I SUM cells & 1 is empty I need the result to be empty not 0 | Excel Discussion (Misc queries) | |||
How can I convert empty strings to empty cells? | Excel Discussion (Misc queries) | |||
Can blank cells created using empty Double-Quotes not be empty?? | Excel Programming |