How to process selected rows

Hi,
I'm trying to process rows that a user has selected before starting the
macro.
I have managed to get the process running by using ActiveCell but that
doesn't seem to know where the other selected rows are, just the first one.

I have looked at collections and shapes but i don't seem to get any values
or objects returned.
I'm probably missing something very obvious here,

Thanks in advance

Ron
(drop the xxx's to reply directly)

Ron

try looking at selection.

For example:

For Each cell In Selection
' do something
next 'cell

Regards

Trevor


"Ron" <[email protected]> wrote in message
news:42efef0f.0@entanet...
> Hi,
> I'm trying to process rows that a user has selected before starting the
> macro.
> I have managed to get the process running by using ActiveCell but that
> doesn't seem to know where the other selected rows are, just the first
> one.
>
> I have looked at collections and shapes but i don't seem to get any values
> or objects returned.
> I'm probably missing something very obvious here,
>
> Thanks in advance
>
> Ron
> (drop the xxx's to reply directly)
>

Thanks,

I'd already tried that but I seem to be breaking the selected area by using
the active cell,
Another part of the routine resets the active cell so that is probaly why it
all falls apart.

Cheers

Ron

"Trevor Shuttleworth" <[email protected]> wrote in message
news:%[email protected]...
> Ron
>
> try looking at selection.
>
> For example:
>
> For Each cell In Selection
> ' do something
> next 'cell
>
> Regards
>
> Trevor
>
>
> "Ron" <[email protected]> wrote in message
> news:42efef0f.0@entanet...
>> Hi,
>> I'm trying to process rows that a user has selected before starting the
>> macro.
>> I have managed to get the process running by using ActiveCell but that
>> doesn't seem to know where the other selected rows are, just the first
>> one.
>>
>> I have looked at collections and shapes but i don't seem to get any
>> values or objects returned.
>> I'm probably missing something very obvious here,
>>
>> Thanks in advance
>>
>> Ron
>> (drop the xxx's to reply directly)
>>
>
>

Hi again,

I'm trying to process rows that a user has selected before starting the
macro.

I've tried selection but it doesn't do what i want.
I need the row addresses for the selected lines.
The selection object gets damaged during the row processing and saving it to
a local variable doesn't seem to work - it stays as an empty variable.

Can anyone help ?

Thanks,

Ron


"Trevor Shuttleworth" <[email protected]> wrote in message
news:%[email protected]...
> Ron
>
> try looking at selection.
>
> For example:
>
> For Each cell In Selection
> ' do something
> next 'cell
>
> Regards
>
> Trevor
>
>
> "Ron" <[email protected]> wrote in message
> news:42efef0f.0@entanet...
>> Hi,
>> I'm trying to process rows that a user has selected before starting the
>> macro.
>> I have managed to get the process running by using ActiveCell but that
>> doesn't seem to know where the other selected rows are, just the first
>> one.
>>
>> I have looked at collections and shapes but i don't seem to get any
>> values or objects returned.
>> I'm probably missing something very obvious here,
>>
>> Thanks in advance
>>
>> Ron
>> (drop the xxx's to reply directly)
>>
>
>

Are they selecting multiple areas (non-contiguous rows) ?

What are you doing during the processing?

try something like

'**********************************
dim rngSelection as range
dim x as integer, r

set rngSelection = selection 'save the selection

for x=1 to rngSelection.areas.count
for each r in rngSelection.areas(x).rows
'process the row (r)
next r
next x
'**********************************

Tim



--
Tim Williams
Palo Alto, CA


"Ron" <[email protected]> wrote in message
news:42efef0f.0@entanet...
> Hi,
> I'm trying to process rows that a user has selected before starting the
> macro.
> I have managed to get the process running by using ActiveCell but that
> doesn't seem to know where the other selected rows are, just the first
one.
>
> I have looked at collections and shapes but i don't seem to get any values
> or objects returned.
> I'm probably missing something very obvious here,
>
> Thanks in advance
>
> Ron
> (drop the xxx's to reply directly)
>
>

Hi Ron,

> I've tried selection but it doesn't do what i want.
> I need the row addresses for the selected lines.
> The selection object gets damaged during the row processing and saving it
> to a local variable doesn't seem to work - it stays as an empty variable.

Perhaps you need to explain what is that you wish to do with the selected
rows, but perhaps you could adapt one of the following:

Dim Rng As Range
Dim rw As Range
Dim i As Long

Set Rng = Selection

'To iterate the selected rows
For Each rw In Rng.Rows
' do something e.g.:
MsgBox rw.Address
Next

'Or, to selectively delete rows:
For i = Rng.Rows.Count To 1 Step -1
If IsEmpty(Rng.Rows(i).Cells(1)) Then
Rng.Rows(i).EntireRow.Delete
End If
Next i

---
Regards,
Norman



"Ron" <[email protected]> wrote in message
news:42f00f2e.0@entanet...
> Hi again,
>
> I'm trying to process rows that a user has selected before starting the
> macro.
>
> I've tried selection but it doesn't do what i want.
> I need the row addresses for the selected lines.
> The selection object gets damaged during the row processing and saving it
> to a local variable doesn't seem to work - it stays as an empty variable.
>
> Can anyone help ?
>
> Thanks,
>
> Ron
>
>
> "Trevor Shuttleworth" <[email protected]> wrote in message
> news:%[email protected]...
>> Ron
>>
>> try looking at selection.
>>
>> For example:
>>
>> For Each cell In Selection
>> ' do something
>> next 'cell
>>
>> Regards
>>
>> Trevor
>>
>>
>> "Ron" <[email protected]> wrote in message
>> news:42efef0f.0@entanet...
>>> Hi,
>>> I'm trying to process rows that a user has selected before starting the
>>> macro.
>>> I have managed to get the process running by using ActiveCell but that
>>> doesn't seem to know where the other selected rows are, just the first
>>> one.
>>>
>>> I have looked at collections and shapes but i don't seem to get any
>>> values or objects returned.
>>> I'm probably missing something very obvious here,
>>>
>>> Thanks in advance
>>>
>>> Ron
>>> (drop the xxx's to reply directly)
>>>
>>
>>
>
>

Thanks Guys,

Both solutions worked for selected cells, but Norman's version led me to the
right result for me
as I can allow the user to select cells in a row or the whole row and still
get the row processed without throwing an error.

My problem seems to lie in not having a good object model to refer to.
The VBA IDE gives rather fragmented help.
Nice to know there are people who do know how it all works ! .
Thanks again

Ron

"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Ron,
>
>> I've tried selection but it doesn't do what i want.
>> I need the row addresses for the selected lines.
>> The selection object gets damaged during the row processing and saving it
>> to a local variable doesn't seem to work - it stays as an empty variable.
>
> Perhaps you need to explain what is that you wish to do with the selected
> rows, but perhaps you could adapt one of the following:
>
> Dim Rng As Range
> Dim rw As Range
> Dim i As Long
>
> Set Rng = Selection
>
> 'To iterate the selected rows
> For Each rw In Rng.Rows
> ' do something e.g.:
> MsgBox rw.Address
> Next
>
> 'Or, to selectively delete rows:
> For i = Rng.Rows.Count To 1 Step -1
> If IsEmpty(Rng.Rows(i).Cells(1)) Then
> Rng.Rows(i).EntireRow.Delete
> End If
> Next i
>
> ---
> Regards,
> Norman
>
>
>
> "Ron" <[email protected]> wrote in message
> news:42f00f2e.0@entanet...
>> Hi again,
>>
>> I'm trying to process rows that a user has selected before starting the
>> macro.
>>
>> I've tried selection but it doesn't do what i want.
>> I need the row addresses for the selected lines.
>> The selection object gets damaged during the row processing and saving it
>> to a local variable doesn't seem to work - it stays as an empty variable.
>>
>> Can anyone help ?
>>
>> Thanks,
>>
>> Ron
>>
>>
>> "Trevor Shuttleworth" <[email protected]> wrote in message
>> news:%[email protected]...
>>> Ron
>>>
>>> try looking at selection.
>>>
>>> For example:
>>>
>>> For Each cell In Selection
>>> ' do something
>>> next 'cell
>>>
>>> Regards
>>>
>>> Trevor
>>>
>>>
>>> "Ron" <[email protected]> wrote in message
>>> news:42efef0f.0@entanet...
>>>> Hi,
>>>> I'm trying to process rows that a user has selected before starting the
>>>> macro.
>>>> I have managed to get the process running by using ActiveCell but that
>>>> doesn't seem to know where the other selected rows are, just the first
>>>> one.
>>>>
>>>> I have looked at collections and shapes but i don't seem to get any
>>>> values or objects returned.
>>>> I'm probably missing something very obvious here,
>>>>
>>>> Thanks in advance
>>>>
>>>> Ron
>>>> (drop the xxx's to reply directly)
>>>>
>>>
>>>
>>
>>
>
>