Excel Find and Replace
November 18, 2011 10:36 AM Subscribe
Excel - Find and Replace -- I use the excel function find and replace often. One spreadsheet finds it instantly. Another searches each cell and takes longer. I stopped updating the instant one before I noticed the effect. Windows xp -- Office 2007
I'd like to have more of the instant search.
Response by poster: I use these to track orders. The instant search last had information added in 2009. The other is current. As far as I know that's the only difference.
posted by notned at 11:00 AM on November 18, 2011
posted by notned at 11:00 AM on November 18, 2011
How much bigger is one than the other? If they're reasonably close in size, try highlighting, say, the first 5000 rows of data and doing the S/R just within the selection; then do the same thing in the other one.
What might be happening is that Excel is for the fast search, it's only searching the range of cells that actually contain data, whereas for the slower one it's looking at the entire sheet for whatever reason.
posted by Doofus Magoo at 11:40 AM on November 18, 2011
What might be happening is that Excel is for the fast search, it's only searching the range of cells that actually contain data, whereas for the slower one it's looking at the entire sheet for whatever reason.
posted by Doofus Magoo at 11:40 AM on November 18, 2011
Does not much difference cover file size?
posted by Lesser Shrew at 11:43 AM on November 18, 2011
posted by Lesser Shrew at 11:43 AM on November 18, 2011
Best answer: And you might want to delete unused rows and columns, just for kicks.
posted by Lesser Shrew at 11:44 AM on November 18, 2011
posted by Lesser Shrew at 11:44 AM on November 18, 2011
Best answer: If it is a Last Cell issue......
Excel Last Cell can be a funny thing. There are a couple of ways to clear out unused rows and columns, but that does not always guarantee it will set it right.
It is a little overkill, but when I run into an issue with Last Cell and I absolutely want to be sure, I create a new sheet and only copy over the active cells of the questionable sheet to the new one. I delete the old sheet and rename the new sheet the same as the deleted one so any links will continue to work.
posted by lampshade at 12:16 PM on November 18, 2011 [1 favorite]
Excel Last Cell can be a funny thing. There are a couple of ways to clear out unused rows and columns, but that does not always guarantee it will set it right.
It is a little overkill, but when I run into an issue with Last Cell and I absolutely want to be sure, I create a new sheet and only copy over the active cells of the questionable sheet to the new one. I delete the old sheet and rename the new sheet the same as the deleted one so any links will continue to work.
posted by lampshade at 12:16 PM on November 18, 2011 [1 favorite]
Useful tip, lampshade!
posted by Lesser Shrew at 2:57 PM on November 18, 2011
posted by Lesser Shrew at 2:57 PM on November 18, 2011
Response by poster: I copied my cells into a new sheet. There were a lot. Now I have instant search and I am happy.
posted by notned at 4:57 PM on November 28, 2011
posted by notned at 4:57 PM on November 28, 2011
This thread is closed to new comments.
posted by dfriedman at 10:54 AM on November 18, 2011