Slow Macro

[Frank Situmorang]

Hello,

I found it that my following VBA makes it slow. Could you please help me how
can we make it faster, bacause we will do for huge worksheets and 100 of
workbooks.
Selection.Replace What:="", Replacement:="$$$$$", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="$$$$$", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Out of my whole VBA:

Sub frankmodi()
Windows("M10-7-012 MORIC INDONESIA.xls").Activate
Sheets("PO New (2)").Copy After:=Sheets(2)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Columns("A:AV").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.Replace What:="", Replacement:="$$$$$", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="$$$$$", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

We appreciate your help.

Frank

 

[JLatham]

There are a couple of things you can do to speed it up. One big, easy one is
to put
Application.ScreenUpdating = False
ahead of everything - as the first statement in the sub. Then at the end,
right before End Sub, put
Application.ScreenUpdating = True

you won't see all the screen flashing and changing of values on the sheet(s)
while that is happening, but by not having to update the display, the process
will run much faster.

The next thing you could do, if that simple fix isn't enough, is to consider
using objects to refer to the new sheet(s) and ranges on them that you are
working with. This is another performance improvement.

Also, you are selecting all cells (the Cells.Select statement) on the
worksheet. You could probably get a performance improvement by fine-tuning
the routine to only select the used cells. There are a couple of ways to do
that, but a quick way would be using this instead of Cells.Select in your code

Range("A1:" & Selection.SpecialCells(xlCellTypeLastCell).Address).Select

you may still pick up some empty cells, especially after deleting columns
A:AV, but it will be minimized.

 

[Dave Peterson]

Sometimes changing calculation to manual, doing the change, then turning
calculation back to automatic can speed things up.

Other times, it doesn't help much.

 

[par_60056]

Sometimes changing calculation to manual, doing the change, then turning
calculation back to automatic can speed things up.

Other times, it doesn't help much.

I thought I posted this reply earlier but it never showed up.

I am curious what the purpose of the 2 replace commands is. Since
they are one after the other, selection hasn't changed and the second
appears to undo the first.

Also, since the first replace command uses an empty string for the
WHAT, won't that result in a large number of replacements?

Peter

 

[JLatham]

I meant to ask that same question - the second replace does appear to undo
all of what the first one did.

 

[Dave Peterson]

If you have lots of formulas that evaluate to =""

=if(a1=1,"ok","")

And then convert to values, the cells that evaluate to "" look empty, but aren't
empty.

You can see this with =len() and =counta().
=len() will return 0 and =counta() will return 1.

One way to really empty these cells is to do this pair of mass changes.

 

[par_60056]

If you have lots of formulas that evaluate to =""

=if(a1=1,"ok","")

And then convert to values, the cells that evaluate to "" look empty, but aren't
empty.

You can see this with =len() and =counta().
=len() will return 0 and =counta() will return 1.

One way to really empty these cells is to do this pair of mass changes.







--

Dave Peterson- Hide quoted text -

- Show quoted text -

Since the selection is all the cells this is going to change a lot of
things and if it really needs to be done for some reason I would
certainly turn or recalculating and screenupdating.

Peter

 

[Dave Peterson]

edit|replace is limited to the usedrange--but that could be a large area, too.