Copy and paste values into next sheet. (blank cells contain formulas)

Hi everyone,

I've been fighting with this since yesterday and everything I've come up with search has not worked out. The reason for this is because I am trying to copy data from one sheet to another, the pasted data should always fall under the previous paste. In theory this works except the data that it is copying from has formulas in the cells, they result to "" which when pasted, is technically not blank to excel. This means when I press End + down arrow or have a macro go to last row with data in it, it over shoots the data filled cells and has a gap of a few hundred blank cells.

As an example. Sheet1 has data from A1:E200. The cells all contain formulas and result to "" (visually blank) when not used. When the cells have the required data in them they actually show information. All I want it is to copy the visual data you could say into the next sheet.

I don't want to copy the entire row over either as in the actual application of this there is surrounding data, so only A1:E200 in this case.

Any ideas?

This is the snippet of the code I am using to accomplish this presently. It works great minus the fact Excel sees the blank spaces as data cells.

Please Login or Register  to view this content.

I think I found a potential solution.

I search for the blanks, input deletethis into it and then search for deletethis and put a blank.

Please Login or Register  to view this content.

Is there a more elegant way to do this?