Can I Find and Relace only a selection?

[Opus97]

I had a problem previous to this that I posted. It had to do with the fact that Excel leaves some kind of fingerprint in a cell when you paste values from a blank cell or paste "". Whatever it leaves, the cell is not blank and sorts with a higher value than all letters of the alphabet. After frying a number of brain cells, I discovered that I could actually paste some unique value and then search and replace with nothing. This does in effect render the cell "empty" or "clear". This allows my sort to work as I want, which is to have the blank cells sort with a lower sort value either ascending or descending. This all needs to be done from within a macro which is the reason for appearing here. Now for my question.

I am using version 2007. Is it no longer possible to find and replace only selected cells? This seems to have disappeared from Excel. The only options are Sheet or Workbook. My sheet is very large and Excel takes a long time searching the entire sheet for my unique string after it first quickly replaces within my selected cells.

Thanx for any help.

[patel45]

You can do it for sure with a macro

Please Login or Register  to view this content.

[FDibbins]

Hi and welcome to the forum

Before "using" F&R, highlight the range you want, then use F&R

[Opus97]

This is what I get when I try to run that code in Excel 2007.

Run-time error 438

Object doesn't support this property or method

[patel45]

I'm sorry, change to

Please Login or Register  to view this content.

[Opus97]

Excel 2007 (or at least my version) does not recognize the above code as a valid macro. It is listed in the macros, but when I select it, everything is greyed out except "Create".

[patel45]

did you put the code in modules ?

[Opus97]

Yes. It follows and is listed with the other macros.

If you have a version of excel that allows find & replace of just the selection, could you tell me what options are listed under "Within" when you expand the "Replace Options"? Is the code you have supplied a result of automatically recording the movements of your mouse and keyboard while performing a Find and Replace operation? Could you go ahead and record those movements, including the selection of a group of cells, so that I might see what excel writes for itself?

Thanx for your help.

[patel45]

I did not use macro recorder, try creating a new module or changing the sub name