Speed of Replace Range

[Monk]

I am trying to replace the values of a large range of cells with the value of
another cell (z5). However it takes a long time to replace the information
given the size of the range. It seems to be updating each cell individually.
Any suggestions would be appreciated. Thanks. Current code is:

Application.Calculation = xlCalculationManual
Range("A11000").Select
Selection.Replace What:="Richard", Replacement:=Range("z5").Value,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
Application.Calculation = xlCalculationAutomatic

 

[Frank RoadRunner]

I tried to solve the same problem as you and I found the code below is
working perfectly:

Sub REP()
Application.Calculation = xlCalculationManual
[A11000].Select
Selection.Replace What:="Richard", Replacement:=[r1].Value, LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False

Application.Calculation = xlCalculationAutomatic

End Sub

I put 25 in the cell R1. Expression [r1] is the same as range("r1").

Monk píše:

 

[Dave Peterson]

Maybe you have a worksheet event firing for each change?

application.enableevents = false
'your code that does the change
application.enableevents = true

 

[Monk]

Hi Dave

I am still experiencing difficulty. Perhaps I should expand. Below is the
full code. When I am changing the word Richard to the value in Z5, the idea
is to change the reference to an external spreadsheet and then update the
value from that spreadsheet. The value in Z5 will be another person's name
and will pick up the external data in that person's file location. The
formula in each of the A11000 cells is as follows:

=IF('F:\Home\Richard''s Practice\Trading\4. Client Lists\KAS PAS\[KAS PAS
Client List.xls]Sheet1'!A1="","",'F:\Home\Richard''s Practice\Trading\4.
Client Lists\KAS PAS\[KAS PAS Client List.xls]Sheet1'!A1)

When I run the code Excel freezes, presumably trying to update each cell
individually. Any errors you can see or whether it can actually be updated
quickly would be appreciated.

Current Code is

Sub UnhideKAS()
'
' UnhideKAS Macro
' Macro recorded 11/02/2006 by Paul Nagle
'


Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("KAS Client List").Visible = True
Sheets("KAS Client List").Select
Range("z2:z5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A11000").Select
Selection.Replace What:="Richard", Replacement:=Range("z5").Value,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
Sheets("KAS Client List").Visible = False
Sheets("KAS Cash").Visible = True
Sheets("KAS Allocations").Visible = True
Sheets("KAS Existing Holdings").Visible = True
Sheets("Menu").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Range("a1").Select

'Retrieve file name to use for Save
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng2 As Range
Dim Rng3 As Range
Dim aStr As String
Dim sPath As String
Dim FName As String

Set WB = ThisWorkbook
Set SH = WB.Sheets("KAS Allocations") '<<==== CHANGE
Set Rng2 = SH.Range("B1") '<<==== CHANGE

If Not IsEmpty(Rng2.Value) Then
aStr = Rng2.Value
FName = aStr
ThisWorkbook.SaveAs Filename:=FName, _
FileFormat:=xlWorkbookNormal

Else
'Your code to handle misssing data, e.g.:
MsgBox Prompt:="Missing Data", _
Buttons:=vbCritical, _
Title:="Problem"



End If
Range("B1").Select
Selection.ClearContents
Range("h2").Select
Sheets("KAS Cash").Select
Range("a1").Select
End Sub

 

[Dave Peterson]

The more cells that grab info from the other file, the slower the replace will
be. And (just a guess), I bet the F: drive is a network drive--not a local
drive. That should slow things down a bit, too.

I think I'd try opening the "after change" workbook. I would think that would
make the recalculation much quicker.

I'm guessing that when you make the mass change, excel will see that the sending
file is open and adjust the formula (by removing the path). But since the
sending workbook is open, the recalc will be faster.

Hi Dave

I am still experiencing difficulty. Perhaps I should expand. Below is the
full code. When I am changing the word Richard to the value in Z5, the idea
is to change the reference to an external spreadsheet and then update the
value from that spreadsheet. The value in Z5 will be another person's name
and will pick up the external data in that person's file location. The
formula in each of the A11000 cells is as follows:

=IF('F:\Home\Richard''s Practice\Trading\4. Client Lists\KAS PAS\[KAS PAS
Client List.xls]Sheet1'!A1="","",'F:\Home\Richard''s Practice\Trading\4.
Client Lists\KAS PAS\[KAS PAS Client List.xls]Sheet1'!A1)

When I run the code Excel freezes, presumably trying to update each cell
individually. Any errors you can see or whether it can actually be updated
quickly would be appreciated.

Current Code is

Sub UnhideKAS()
'
' UnhideKAS Macro
' Macro recorded 11/02/2006 by Paul Nagle
'

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("KAS Client List").Visible = True
Sheets("KAS Client List").Select
Range("z2:z5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A11000").Select
Selection.Replace What:="Richard", Replacement:=Range("z5").Value,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
Sheets("KAS Client List").Visible = False
Sheets("KAS Cash").Visible = True
Sheets("KAS Allocations").Visible = True
Sheets("KAS Existing Holdings").Visible = True
Sheets("Menu").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Range("a1").Select

'Retrieve file name to use for Save
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng2 As Range
Dim Rng3 As Range
Dim aStr As String
Dim sPath As String
Dim FName As String

Set WB = ThisWorkbook
Set SH = WB.Sheets("KAS Allocations") '<<==== CHANGE
Set Rng2 = SH.Range("B1") '<<==== CHANGE

If Not IsEmpty(Rng2.Value) Then
aStr = Rng2.Value
FName = aStr
ThisWorkbook.SaveAs Filename:=FName, _
FileFormat:=xlWorkbookNormal

Else
'Your code to handle misssing data, e.g.:
MsgBox Prompt:="Missing Data", _
Buttons:=vbCritical, _
Title:="Problem"



End If
Range("B1").Select
Selection.ClearContents
Range("h2").Select
Sheets("KAS Cash").Select
Range("a1").Select
End Sub

Maybe you have a worksheet event firing for each change?

application.enableevents = false
'your code that does the change
application.enableevents = true

 

[Monk]

Thanks Dave

Preliminary Testing indicates that may work well. Could I code the macro to
open a network drive file based on the name that appears in the z5 cell
reference?

i.e Open F:\Home\z5 cell reference''s Practice\Trading\4. Client Lists\KAS
PAS\[KAS PAS Client List.xls)

The more cells that grab info from the other file, the slower the replace will
be. And (just a guess), I bet the F: drive is a network drive--not a local
drive. That should slow things down a bit, too.

I think I'd try opening the "after change" workbook. I would think that would
make the recalculation much quicker.

I'm guessing that when you make the mass change, excel will see that the sending
file is open and adjust the formula (by removing the path). But since the
sending workbook is open, the recalc will be faster.

Hi Dave

I am still experiencing difficulty. Perhaps I should expand. Below is the
full code. When I am changing the word Richard to the value in Z5, the idea
is to change the reference to an external spreadsheet and then update the
value from that spreadsheet. The value in Z5 will be another person's name
and will pick up the external data in that person's file location. The
formula in each of the A11000 cells is as follows:

=IF('F:\Home\Richard''s Practice\Trading\4. Client Lists\KAS PAS\[KAS PAS
Client List.xls]Sheet1'!A1="","",'F:\Home\Richard''s Practice\Trading\4.
Client Lists\KAS PAS\[KAS PAS Client List.xls]Sheet1'!A1)

When I run the code Excel freezes, presumably trying to update each cell
individually. Any errors you can see or whether it can actually be updated
quickly would be appreciated.

Current Code is

Sub UnhideKAS()
'
' UnhideKAS Macro
' Macro recorded 11/02/2006 by Paul Nagle
'

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("KAS Client List").Visible = True
Sheets("KAS Client List").Select
Range("z2:z5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A11000").Select
Selection.Replace What:="Richard", Replacement:=Range("z5").Value,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
Sheets("KAS Client List").Visible = False
Sheets("KAS Cash").Visible = True
Sheets("KAS Allocations").Visible = True
Sheets("KAS Existing Holdings").Visible = True
Sheets("Menu").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Range("a1").Select

'Retrieve file name to use for Save
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng2 As Range
Dim Rng3 As Range
Dim aStr As String
Dim sPath As String
Dim FName As String

Set WB = ThisWorkbook
Set SH = WB.Sheets("KAS Allocations") '<<==== CHANGE
Set Rng2 = SH.Range("B1") '<<==== CHANGE

If Not IsEmpty(Rng2.Value) Then
aStr = Rng2.Value
FName = aStr
ThisWorkbook.SaveAs Filename:=FName, _
FileFormat:=xlWorkbookNormal

Else
'Your code to handle misssing data, e.g.:
MsgBox Prompt:="Missing Data", _
Buttons:=vbCritical, _
Title:="Problem"



End If
Range("B1").Select
Selection.ClearContents
Range("h2").Select
Sheets("KAS Cash").Select
Range("a1").Select
End Sub

Maybe you have a worksheet event firing for each change?

application.enableevents = false
'your code that does the change
application.enableevents = true



Monk wrote:

I am trying to replace the values of a large range of cells with the value of
another cell (z5). However it takes a long time to replace the information
given the size of the range. It seems to be updating each cell individually.
Any suggestions would be appreciated. Thanks. Current code is:

Application.Calculation = xlCalculationManual
Range("A11000").Select
Selection.Replace What:="Richard", Replacement:=Range("z5").Value,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
Application.Calculation = xlCalculationAutomatic

 

[Monk]

Thanks Dave. I have found a solution which seems to work well. Your
assistance is much appreciated.

The more cells that grab info from the other file, the slower the replace will
be. And (just a guess), I bet the F: drive is a network drive--not a local
drive. That should slow things down a bit, too.

I think I'd try opening the "after change" workbook. I would think that would
make the recalculation much quicker.

I'm guessing that when you make the mass change, excel will see that the sending
file is open and adjust the formula (by removing the path). But since the
sending workbook is open, the recalc will be faster.

Hi Dave

I am still experiencing difficulty. Perhaps I should expand. Below is the
full code. When I am changing the word Richard to the value in Z5, the idea
is to change the reference to an external spreadsheet and then update the
value from that spreadsheet. The value in Z5 will be another person's name
and will pick up the external data in that person's file location. The
formula in each of the A11000 cells is as follows:

=IF('F:\Home\Richard''s Practice\Trading\4. Client Lists\KAS PAS\[KAS PAS
Client List.xls]Sheet1'!A1="","",'F:\Home\Richard''s Practice\Trading\4.
Client Lists\KAS PAS\[KAS PAS Client List.xls]Sheet1'!A1)

When I run the code Excel freezes, presumably trying to update each cell
individually. Any errors you can see or whether it can actually be updated
quickly would be appreciated.

Current Code is

Sub UnhideKAS()
'
' UnhideKAS Macro
' Macro recorded 11/02/2006 by Paul Nagle
'

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("KAS Client List").Visible = True
Sheets("KAS Client List").Select
Range("z2:z5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A11000").Select
Selection.Replace What:="Richard", Replacement:=Range("z5").Value,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
Sheets("KAS Client List").Visible = False
Sheets("KAS Cash").Visible = True
Sheets("KAS Allocations").Visible = True
Sheets("KAS Existing Holdings").Visible = True
Sheets("Menu").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Range("a1").Select

'Retrieve file name to use for Save
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng2 As Range
Dim Rng3 As Range
Dim aStr As String
Dim sPath As String
Dim FName As String

Set WB = ThisWorkbook
Set SH = WB.Sheets("KAS Allocations") '<<==== CHANGE
Set Rng2 = SH.Range("B1") '<<==== CHANGE

If Not IsEmpty(Rng2.Value) Then
aStr = Rng2.Value
FName = aStr
ThisWorkbook.SaveAs Filename:=FName, _
FileFormat:=xlWorkbookNormal

Else
'Your code to handle misssing data, e.g.:
MsgBox Prompt:="Missing Data", _
Buttons:=vbCritical, _
Title:="Problem"



End If
Range("B1").Select
Selection.ClearContents
Range("h2").Select
Sheets("KAS Cash").Select
Range("a1").Select
End Sub

Maybe you have a worksheet event firing for each change?

application.enableevents = false
'your code that does the change
application.enableevents = true



Monk wrote:

I am trying to replace the values of a large range of cells with the value of
another cell (z5). However it takes a long time to replace the information
given the size of the range. It seems to be updating each cell individually.
Any suggestions would be appreciated. Thanks. Current code is:

Application.Calculation = xlCalculationManual
Range("A11000").Select
Selection.Replace What:="Richard", Replacement:=Range("z5").Value,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
Application.Calculation = xlCalculationAutomatic