Solved: WITH statement must be inside Do...Loop?

[Digita]

Please refer to the sample codes below, it appears that the WITH statement cannot be wrapped outside the do loop. Semantically, I don't see any real difference between the 2 versions.

This one does not work

With ActiveCell
Do
If .Hyperlinks.Count > 0 And .Value <> Empty Then .Hyperlinks.Delete
.Offset(1, 0).Select
Loop Until .Value = Empty
End With


This one works
Do
With ActiveCell
If .Hyperlinks.Count > 0 And .Value <> Empty Then .Hyperlinks.Delete
.Offset(1, 0).Select
End With
Loop Until ActiveCell = Empty


Regards

[mdmackillop]

Your With statement reads like this, so loops endlessly as the Activecell never changes.

With ActiveCell

Do

If .Hyperlinks.Count > 0 And .Value <> Empty Then .Hyperlinks.Delete
.Offset(1, 0).Select


Loop Until .Value = Empty
End With



Try to avoid selecting cells though. It greatly slows up code execution


Sub test()
Dim i as long
Do
With ActiveCell.Offset(i)
If .Hyperlinks.Count > 0 And .Value <> Empty Then .Hyperlinks.Delete
i = i + 1
End With
Loop Until ActiveCell.Offset(i) = Empty
End Sub

[Bob Phillips]

When you have a loop, the With invariably needs to be inside the loop as the With is resolved immediately, and just the once, so each iteration of the loop would refer to the same object, it is not dynamically updated per iteration. So, to refer to the next object in the set, the With has to be reset within the loop.

[Digita]

Indeed, selecting cells inside the loop really slows down code execution to the point I thought my PC was frozen. Thanks for the great tip Mal.

Thank you both for your insights. There are always new things to learn from the gurus.

Have a great day.

Regards



kp