Preventing White Screen Flickering (App.ScrnUpdting) = False... Etc...

indi visual · Feb 13, 2011

I've tried all of the following they work great. But when running a particularly lengthy piece of code my screens completely goes white temporarily (until it finishes past this long code). Performance wise my PC is pretty tough, so the fault is in my code somewhere. Is there anything else I can do to prevent this other than the items beneath?

I guess I should ask, which of these are really unnecessary(because I've just been using all of them under the assumption it could only help make things faster).

Application.ScreenUpdating = False

Application.Calculation = xlManual

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False

Application.DisplayAlerts = False

Application.StatusBar = False

Application.WAIT Now + TimeValue("00:00:01")

Here is an example of the long multiple search and replaces I'm running when the screen goes white.

I should warn anyone who looks at this it's organized with notes, but ridiculously long. I'm also open to any ideas to make this faster (the search and replaces MUST be ran in the order they appear though).

http://www.iandmyself.me/Snippet.It.Flickers.On.txt

http://www.iandmyself.me/Entire.Module.txt

What this code does is places start & end markers at the beginning and end of suspected links, and then deletes them.

Hui · Feb 14, 2011

A few points here

1. You have defined MyRange as Set MyRange = Range("A2", Range("A65536").End(xlUp))

but then you dont use it ?

[pre]

Code:

eg:
Range(&#34;A1&#34;, Range(&#34;A65536&#34;).End(xlUp)).AutoFilter Field:=1, Criteria1:=&#34;=*.*&#34;
should be
MyRange.AutoFilter Field:=1, Criteria1:=&#34;=*.*&#34;
2. Problem is you are doing a lot(252) reads and then writes of your data to/from the worksheet

This can be simplified by reading all the data into an array and then doing the compare in VBA and then writing it out again all in one pass


I am reluctant to show you this because it will create more questions than I'm sure it is worth, but it will fix your problem

[pre][code]Sub h()
Dim i As Integer
Dim myRange() As Variant
ReDim myRange(1 To Range(&#34;A2&#34;, Range(&#34;A65536&#34;).End(xlUp)).Count, 1)

myRange = Range(&#34;A2&#34;, Range(&#34;A65536&#34;).End(xlUp)) &#39;This will read all data into myRange in one pass
For i = LBound(myRange) To UBound(myRange)
myRange(i, 1) = Replace(myRange(i, 1), &#34;.ac &#34;, &#34;URLExtentionMarker&#34;)
myRange(i, 1) = Replace(myRange(i, 1), &#34;.ad &#34;, &#34;URLExtentionMarker&#34;)
myRange(i, 1) = Replace(myRange(i, 1), &#34;.aero &#34;, &#34;URLExtentionMarker&#34;)
myRange(i, 1) = Replace(myRange(i, 1), &#34;.ae &#34;, &#34;URLExtentionMarker&#34;)
&#39;etc
&#39;see my next post for the rest of the codes
Next

Range(&#34;A2&#34;, Range(&#34;A65536&#34;).End(xlUp)).Value = myRange &#39;This will write all data back in one pass

End Sub

[/pre]

==========

IMPORTANT

A Practice Exercise.

Now before you go and jump in I would suggest you setup a new workbook

put some values values in a2:a20 (doesn't really mater how many)

Try the following code

Sub h2()
Dim i As Integer
Dim myRange() As Variant
ReDim myRange(1 To Range("A2", Range("A65536&#34

.End(xlUp)).Count, 1)

myRange = Range("A2", Range("A65536&#34

.End(xlUp)) 'This reads the data

For i = LBound(myRange) To UBound(myRange)
myRange(i, 1) = i * myRange(i, 1)
Next

Range("A2", Range("A65536&#34

.End(xlUp)).Value = myRange 'This writes it back
End Sub[/code][/pre]
Now PLEASE work through this last example until you understand what it is doing

Hui · Feb 14, 2011

and you might need this later on

myRange(i, 1) = Replace(myRange(i, 1),".ad ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ad ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".aero ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ae ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".af ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ag ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ai ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".al ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".am ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".an ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ao ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".aq ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ar ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".asia ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".as ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".at ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".au ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".aw ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ax ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".az ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ba ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".bb ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".bd ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".be ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".bf ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".bg ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".bh ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".bi ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".biz ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".bj ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".bm ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".bn ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".bo ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".br ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".bs ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".bt ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".bv ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".bw ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".by ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".bz ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ca ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".cat ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".cc ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".cd ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".cf ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".cg ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ch ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ci ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ck ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".cl ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".cm ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".cn ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".com ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".coop ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".co ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".cr ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".cs ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".cu ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".cv ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".cx ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".cy ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".cz ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".de ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".dj ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".dk ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".dm ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".do ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".dz ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".edu ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ee ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".eg ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".eh ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".er ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".es ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".et ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".eu ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".fj ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".fk ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".fm ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".fo ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".fr ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gb ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gd ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ge ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gf ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gg ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gh ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gi ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gl ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gm ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gn ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gov ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gp ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gq ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gr ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gs ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gt ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gu ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gw ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".gy ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".hm ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".hn ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".hr ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".htm ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ht ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".hu ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ie ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".il ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".im ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".info ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".int ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".in ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".io ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".iq ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ir ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".is ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".it ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".jm ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".jobs ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".jo ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".jp ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".kg ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".kh ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ki ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".km ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".kn ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".kp ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".kr ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".kw ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ky ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".kz ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".lb ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".lc ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".li ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".lk ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".lr ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ls ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".lt ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".lu ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".lv ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ly ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mc ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".md ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".me ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mg ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mh ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mil ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mk ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ml ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mm ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mn ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mobi ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mo ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mp ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mq ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mr ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ms ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mt ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".museum ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mu ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mv ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mw ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mx ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".my ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".mz ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".name ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".nc ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".net ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ne ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".nf ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ng ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ni ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".nl ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".no ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".np ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".nr ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".nu ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".nz ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".org ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".pe ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".pf ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".pg ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ph ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".pk ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".pl ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".pm ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".pn ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".pr ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".pro ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ps ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".pt ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".pw ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".py ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ro ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".rs ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ru ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".rw ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".sb ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".sc ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".sd ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".se ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".sg ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".sh ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".si ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".sj ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".sk ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".sl ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".sm ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".sn ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".so ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".sr ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".st ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".su ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".sv ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".sy ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".sz ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".td ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".tel ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".tf ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".tg ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".th ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".tj ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".tk ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".tl ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".tm ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".tn ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".to ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".tp ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".travel ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".tr ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".tt ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".tv ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".tw ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".tz ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ug ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".uk ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".um ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".us ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".uy ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".uz ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".vc ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ve ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".vg ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".vi ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".vn ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".vu ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".ws ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".yt ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".yu ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".zm ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".zr ","URLExtentionMarker&#34

myRange(i, 1) = Replace(myRange(i, 1),".zw ","URLExtentionMarker&#34

indi visual · Feb 14, 2011

Absolutely Beautiful.

My screen no longer flickers, which answers my question and solves my problem.

My understanding of this is it reads everything once, and writes it back once in one pass (an array).

However (here comes the side questions you feared... lol)

-I only have 3 questions, and no more afterward.

1. How do I adjust this to search for both UPPERCASE & lowercase?

*in this particular case the url extentions should all be lowercase, but how do I modify the code to look for either or if needed.

2. Is it faster if I filter the range before I run search-replacements?

*in theory I'm thinking that if you trim the range before you run the search it's faster.

3. If so, how would I filter this range first before running the array?

*what I'm really asking here is how do I set the read & write parameters to only the filtered selected visible cells.

eg:

Dim i As Integer

Dim myRange() As Variant

ReDim myRange(1 To Range("A2", Range("A65536").End(xlUp)).Count, 1)

MyRange = MyFilteredRange.SpecialCells(xlCellTypeVisible).Select 'Reads all data in one pass

Set MyFilteredRange = Range("A2", Range("A65536").End(xlUp)) ''selects all visible filtered cells

With ActiveSheet

Range("A1", Range("A65536").End(xlUp)).AutoFilter Field:=1, Criteria1:="=*.*" 'criteria here trims unecessary range for speed

MyFilteredRange.SpecialCells(xlCellTypeVisible).Select

End With

With Selection

For i = LBound(MyRange) To UBound(MyRange)

MyRange(i, 1) = Replace(MyRange(i, 1), ".ac ", "URLExtentionMarker")

MyRange(i, 1) = Replace(MyRange(i, 1), ".ad ", "URLExtentionMarker")...etc.

End With

Next

Range("A2", Range("A65536").End(xlUp)).Value = myRange 'This writes it back

Luke M · Feb 14, 2011

1. Check out the UCase/LCase methods to compare uppercase and lowercase scenarios.

2. Don't filter it. XL will have to treat the filtered region as several small ranges, grouped together (eg, A2:A4,A5:A7,A9:A10,etc...), rather than 1 large range (eg A2:A10).

3. See #2

indi visual · Feb 14, 2011

I have everything I need.

Thanks Hui

Thanks Luke

Hui you went above and beyond on this one, and I appreciate it.

Hui · Feb 14, 2011

Thanx Luke

@Indi

To clarify your statement "My understanding of this is it reads everything once, and writes it back once in one pass (an array)."

The array is the storage container which holds the data in VBA, in this case using myRange = Range("A2", Range("A65536").End(xlUp))

myRange is the array.

it consists of 1 column of data which holds ubound(myRange)-lbound(myRange) records

Reading and writing the data is not the array.

indi visual · Feb 15, 2011

Okay yeah I see now that makes sense, thanks again guys.

Preventing White Screen Flickering (App.ScrnUpdting) = False... Etc...

indi visual

Member

Hui

Excel Ninja

Hui

Excel Ninja

indi visual

Member

Luke M

Excel Ninja

indi visual

Member

Hui

Excel Ninja

indi visual

Member