Worksheet_Change() and Range question

[Robert Crandal]

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!

 

[Lars-Åke Aspelin]

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

 

[Robert Crandal]

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]

Try this

Target(2).Address

Hope this helps / Lars-Åke