Find & Replace - Limit search to a specific column

[falena23]

I've recently switched from Excel 2000 to Excel 2007. I understood that one
could limit a Find / Replace to a column by selecting that column before CTRL
+ H. This works if you select Replace All - but if you select Find All then
Replace All - then the replace will take place in all columns.
You can see why this is - the focus in the spreadsheet goes from the column
to the first cell found.
Nowhere in HELP is there any information on this. Also checking a couple of
Excel tips sites they suggest selecting the column first but do not warn
about the anomaly above. As I'm working on over a 100,000 rows I find it more
than a nuisance.
Any suggestions on how you can keep Find/Replace working in a single column
will be much appreciated

 

[Wigi]

A line of VBA can do it:

for intance:

Columns("A").Replace What:=1, Replacement:=2, SearchOrder:=xlByColumns

Wigi

 

[falena23]

Hi Wigi,

Thanks for the response . The VBA code has the same effect as running
REPLACE all in FIND/REPLACE.
I did not explain myself well . I wish to inspect the result of a find in
the FIND/REPLACE window before I do any replace and perhaps only
selectively replace.
The FIND/REPLACE window is misleading because it returns only hits in the
selected column but if you do anything other than a REPLACE ALL immediately
after the FIND it can find and or replace cells that were not in the
originally selected column.

 

[Spiky]

It's the Find All that does this. Try using Find Next instead to
scroll through and check all the instances. Then Replace should still
work with your original selection.