If you copy a formula that evaluates to ="" and then paste special|values, the
cell is still not empty.

You could verify this in a new worksheet.

Put this in A1:
=""

put this in B1:
=counta(a1)

You'll see 1. =counta() counts the number of non-empty cells--formulas are
included.

Now, copy|paste special|values for just A1.

B1 still shows 1--excel stills sees that cell as non-empty.

Select A1 and hit the delete key (really clearing the cell). B1 will show 0.
The cell is really empty.

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

===============
There are ways to clean up this stuff (edit|replace is one).

But that technique that I suggested has worked fine for me--and other people.

If you tried it (at least twice) and it failed, you could have posted a followup
where it failed--or even ask for clarification in that thread.





robzrob wrote:

On Dec 13, 4:05 pm, Dave Peterson wrote:
You didn't like yesterday's response?





robzrob wrote:

In Workbook2 I’ve got formulas in all cells from A1 to J100. The
formulas ‘pull in’ values from the corresponding cells in Workbook1.
Not all rows in Workbook1 always have values in them, ie the values
may stop at row 20, 30, 45, whatever (but there won’t be any gaps).
I
need to print Workbook2 sometimes and I only want to print the rows
where there are values, not the whole 100 rows with formulas in them,
but if I click ‘Print’ I always get the whole 100 rows printed,
whether there are values in them or not. In the 100 rows I’ve got
conditional formatting thus: If cell isn’t blank, put a border around
it, in Page Set Up, I’ve got Rows to repeat at top: $2:$2 and I’ve
got
a header – which is just some text, but even if I take the
conditional
formatting off and take the Rows to Repeat and header out, I still
get
the 100 rows printed.

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Hello Dave - sorry I couldn't understand it. :(

I've been playing around, trying different things, tried some sites as
suggested by Don above, but nothing works yet. One thing I thought
might work: Macro thus: Select whole worksheet, copy, insert new
worksheet, paste VALUES only to the new worksheet, then print the new
worksheet, but I've tried all those steps (manually, ie not within a
macro) and when I Print Preview the new worksheet (currently I've only
got 43 rows of values) I still get 5 pages (2 pages with the rows with
values in them and 3 blank pages), ie it's still printing the full 100
rows, even though, on the new inserted worksheet, there's nothing -
not even formulas - in rows 44-100 ! ! !

--

Dave Peterson