Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each cell In Selection.....
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each cell In Selection.....
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) "Luc" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each cell In Selection.....
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) "Rick Rothstein" wrote in message ... 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) "Luc" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Limiting selection in a cell AND linking that selection to a list | Excel Discussion (Misc queries) | |||
Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
How to create a selection list then display the selection in a cell | Excel Programming | |||
Change from Column Selection to Cell Selection | Excel Programming | |||
limit cell list selection based on the selection of another list | Excel Worksheet Functions |