Excel - Find & Replace - Limit search to a specific column

Asked By falena2 on 27-Jul-08 08:58 AM
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




Wig replied on 27-Jul-08 12:40 PM
A line of VBA can do it:

for intance:

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

Wigi


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music
falena2 replied on 27-Jul-08 09:26 PM
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 replied on 30-Jul-08 09:58 PM
it is 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.