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)

How to process selected rows
 

Ron

try looking at selection.

For example:

For Each cell In Selection
' do something
next 'cell

Regards

Trevor


"Ron" 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)

How to process selected rows
 

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" wrote in message
...
Ron

try looking at selection.

For example:

For Each cell In Selection
' do something
next 'cell

Regards

Trevor


"Ron" 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)

How to process selected rows
 

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" wrote in message
...
Ron

try looking at selection.

For example:

For Each cell In Selection
' do something
next 'cell

Regards

Trevor


"Ron" 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)

How to process selected rows
 

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" 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" wrote in message
...
Ron

try looking at selection.

For example:

For Each cell In Selection
' do something
next 'cell

Regards

Trevor


"Ron" 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)

How to process selected rows - Resolved
 

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" wrote in message
...
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" 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" wrote in message
...
Ron

try looking at selection.

For example:

For Each cell In Selection
' do something
next 'cell

Regards

Trevor


"Ron" 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)