Find & Replace - Limit search to a specific column
 

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

Find & Replace - Limit search to a specific column
 

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


"falena23" wrote:

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

Find & Replace - Limit search to a specific column
 

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.



"Wigi" wrote:

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


"falena23" wrote:

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

Find & Replace - Limit search to a specific column
 

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.