Replacing formula values with text/raw values

[NicholasL]

So I have a large section of data (standard Deviations) calculated. It's something like 2000x7. I need to delete rows of data but I can't have the standard deviation or rankings change based on this deletion of row. Therefore I need to replace all the formulas for my rankings (something like "=RANK(RC[-.....") so they're instead just the numbers.

Below is a code I wrote up to replace the cell formulas with numbers but it takes a long time. Is there a quick way to replace these with just raw text values instead of formulas?

Thanks!

Please Login or Register  to view this content.

[AB33]

You have two until/Loop and might be infinite, so avoid using until loop, instead use for next loop which is closed.

[NicholasL]

Will the for next loop be quicker?

The loops I have now work fine they just don't work efficiently. I've stepped into them and they go through the rows fine, but it takes forever to go through 28,000 cells and calculate individual values like that. when I let the code run it produces good results, just not in a timely fashion -- so it's not running into any infinite issues.

Is there a way copy a large range of cells just as their text values instead of their formula values, and then paste them right back in?

[AB33]

For example, instead of looping, you can use this line if you copy a fixed range.

Please Login or Register  to view this content.

[NicholasL]

That worked perfectly! I was unaware of the additional options with pastespecial! Thanks!