Worksheet_Change() and Range question
 

If someone highlights a block of cells and presses "delete"
to delete all the data in those cells, Excel will pass all
those changed cell addresses to "Worksheet_Change()" into
the "Target" variable.

My question is, how do I enumerate through all the cells
contained in the "Target" range variable?? The "Target"
variable seems to contain a single string of all the cell
addresses that were changed, and I'm looking for an easy
way to extract each cell address from the "Target" variable.

I initially thought I could use the following code, but I was
wrong:

Public Sub Worksheet_Change (ByVal Target as Range)

for i = 1 to Target.Count
nextAddress = Target(i).Address(0,0) ' Get next changed address??
Next i

End Sub

Thank you!

Worksheet_Change() and Range question
 

On Sat, 12 Dec 2009 15:32:19 -0700, "Robert Crandal"
wrote:

If someone highlights a block of cells and presses "delete"
to delete all the data in those cells, Excel will pass all
those changed cell addresses to "Worksheet_Change()" into
the "Target" variable.

My question is, how do I enumerate through all the cells
contained in the "Target" range variable?? The "Target"
variable seems to contain a single string of all the cell
addresses that were changed, and I'm looking for an easy
way to extract each cell address from the "Target" variable.

I initially thought I could use the following code, but I was
wrong:

Public Sub Worksheet_Change (ByVal Target as Range)

for i = 1 to Target.Count
nextAddress = Target(i).Address(0,0) ' Get next changed address??
Next i

End Sub

Thank you!

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
For Each c In Target
nextaddress = c.Address
Next c
End Sub

Hope this helps / Lars-Åke

Worksheet_Change() and Range question
 

Yes, that works excellent. Just what I was looking for.

BTW, what would be the correct notation to just extract a SINGLE
address? For example, what if I am only interested in the second
cell address in the list??

thankx again


"Lars-Åke Aspelin" wrote in message
...

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
For Each c In Target
nextaddress = c.Address
Next c
End Sub

Worksheet_Change() and Range question
 

Try this

Target(2).Address

Hope this helps / Lars-Åke


On Sat, 12 Dec 2009 16:11:15 -0700, "Robert Crandal"
wrote:

Yes, that works excellent. Just what I was looking for.

BTW, what would be the correct notation to just extract a SINGLE
address? For example, what if I am only interested in the second
cell address in the list??

thankx again


"Lars-Åke Aspelin" wrote in message
.. .

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
For Each c In Target
nextaddress = c.Address
Next c
End Sub