Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change shade of cells when selecting multiple cells | Excel Discussion (Misc queries) | |||
Vlookup - Array not selecting in other workbook | Excel Discussion (Misc queries) | |||
selecting a single sheet from a volume of sheets in a workbook | Excel Worksheet Functions | |||
selecting multiple sheet tabs and open another workbook | Excel Discussion (Misc queries) | |||
Excel workbook fails to display when selecting from my documents. | Excel Worksheet Functions |