Selecting used cells only in the workbook
 

Hi

I am using this code for mass replacing...
For Each wsSheet In Worksheets
wsSheet = Application.ActiveSheet
Cells.Select
Selection.Replace What:="*!", Replacement:="=", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Next wsSheet

Is it possible that instead of "Cells.Select" i can use only used cells in
the worksheet or even in the complete workbook to increase the performance??

Thanks,
Sandeep

Selecting used cells only in the workbook
 

Try this instead of your Cells.Select statement:

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

While this may select some unused cells, it won't select all except under
special circumstances (you've put something over in IV65536 in the past).
Should show some improvement.

"Sandeep" wrote:

Hi

I am using this code for mass replacing...
For Each wsSheet In Worksheets
wsSheet = Application.ActiveSheet
Cells.Select
Selection.Replace What:="*!", Replacement:="=", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Next wsSheet

Is it possible that instead of "Cells.Select" i can use only used cells in
the worksheet or even in the complete workbook to increase the performance??

Thanks,
Sandeep

Selecting used cells only in the workbook
 

Edit|Find and Edit|Replace will only use the usedrange--so don't worry about
that.

But you could drop the selection

For Each wsSheet In Worksheets
wssheet.Cells.Replace What:="*!", Replacement:="=", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next wsSheet

It kind of looks like you're changing strings to formulas.

You may want to turn calculation to manual, loop through the sheets, then turn
the calculation to automatic.

One giant calc may be faster (or not!) than a bunch of smaller calcs.

Sandeep wrote:

Hi

I am using this code for mass replacing...
For Each wsSheet In Worksheets
wsSheet = Application.ActiveSheet
Cells.Select
Selection.Replace What:="*!", Replacement:="=", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Next wsSheet

Is it possible that instead of "Cells.Select" i can use only used cells in
the worksheet or even in the complete workbook to increase the performance??

Thanks,
Sandeep

--

Dave Peterson

Selecting used cells only in the workbook
 

"Edit|Find and Edit|Replace will only use the usedrange..."
SMACKS! forehead <g I knew that.

"Dave Peterson" wrote:

Edit|Find and Edit|Replace will only use the usedrange--so don't worry about
that.

But you could drop the selection

For Each wsSheet In Worksheets
wssheet.Cells.Replace What:="*!", Replacement:="=", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next wsSheet

It kind of looks like you're changing strings to formulas.

You may want to turn calculation to manual, loop through the sheets, then turn
the calculation to automatic.

One giant calc may be faster (or not!) than a bunch of smaller calcs.

Sandeep wrote:

Hi

I am using this code for mass replacing...
For Each wsSheet In Worksheets
wsSheet = Application.ActiveSheet
Cells.Select
Selection.Replace What:="*!", Replacement:="=", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Next wsSheet

Is it possible that instead of "Cells.Select" i can use only used cells in
the worksheet or even in the complete workbook to increase the performance??

Thanks,
Sandeep

--

Dave Peterson

Selecting used cells only in the workbook
 

Hi,

Select all the worksheet in your workbook by pressing the Ctrl key and
selecting each worksheet. This will group all the selected worksheet. Then do
the following:

1. On the Edit menu, click Replace.
The Find and Replace dialog box is displayed.
2. In the Find what text box, type the word you want to search in all the
worksheet.
3. In the Replace with text box, type the word you want to replace in all
the worksheet.
4. Click the Replace All button to replace all the replace at one time.
5. Click Close to return to the Worksheet.
6. Right-click on the Sheet tab and select Ungroup Sheets, to ungroup all
the grouped worksheet in the workbook.

Challa Prabhu




"Sandeep" wrote:

Hi

I am using this code for mass replacing...
For Each wsSheet In Worksheets
wsSheet = Application.ActiveSheet
Cells.Select
Selection.Replace What:="*!", Replacement:="=", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Next wsSheet

Is it possible that instead of "Cells.Select" i can use only used cells in
the worksheet or even in the complete workbook to increase the performance??

Thanks,
Sandeep