View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default For Each cell In Selection.....

Dim myCell as range
dim myRng as range
dim cCtr as long

set myrng = selection.columns(1)

if myrng.cells.count = 1 then
'only one cell selected, do nothing
else
cctr = 0
for each mycell in myrng.cells
cctr = cctr + 1
if cctr = myrng.cells.count then
'do nothing
else
msgbox mycell.address
end if
next mycell
end if

or even...

Dim myCell as range
dim myRng as range
dim mySmallerRng as range
dim cCtr as long

set myrng = selection.columns(1)

set mysmallerrng = nothing
on error resume next
with myrng
set mysmallerrng = .resize(.rows.count-1)
end with
on error goto 0

if mysmallerrng is nothing then
'nothing to do
else
for each mycell in mysmallerrng.cells
msgbox mycell.address
next mycell
end if




Luc wrote:

The selection contains 1 column
The number of rows is variable

If i want to execute the "For Each cell In Selection" command, but i want the
execute this for 1 row less than the actual selection...(the last row is not
to be executed)
How do i do this ?



Thanx,
Luc


--

Dave Peterson