For Each cell In Selection.....

[Luc]

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]

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

 

[Rick Rothstein]

Just resize the range you are looking through...

For Each Cell In Selection.Resize(Selection.Count - 1)

Note that if your selection is just a single cell, this code will generate an error, so you should check for that. May this...

If Selection.Count = 1 Then Exit Sub
For Each Cell In Selection.Resize(Selection.Count - 1)
....
....

or this...

If Selection.Count > 1 Then Exit Sub
For Each Cell In Selection.Resize(Selection.Count - 1)
....
....
End If
....
....

depending on how the rest of your code needs to be handled.

--
Rick (MVP - Excel)


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

 

[Rick Rothstein]

or this...

If Selection.Count > 1 Then Exit Sub
For Each Cell In Selection.Resize(Selection.Count - 1)

That "Exit Sub" should not be there (it was an accidental carry over from a copy/paste). It should have read this way...

or this...

If Selection.Count > 1 Then
For Each Cell In Selection.Resize(Selection.Count - 1)
....
....
End If
....
....

--
Rick (MVP - Excel)


Just resize the range you are looking through...

For Each Cell In Selection.Resize(Selection.Count - 1)

Note that if your selection is just a single cell, this code will generate an error, so you should check for that. May this...

If Selection.Count = 1 Then Exit Sub
For Each Cell In Selection.Resize(Selection.Count - 1)
....
....

or this...

If Selection.Count > 1 Then Exit Sub
For Each Cell In Selection.Resize(Selection.Count - 1)
....
....
End If
....
....

depending on how the rest of your code needs to be handled.

--
Rick (MVP - Excel)


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