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.

http://www.google.com/search?hl=en&r...isible&spell=1

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"robzrob" wrote in message
...
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.

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

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 ! ! !

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

On Dec 13, 5:12*pm, Dave Peterson wrote:
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- Hide quoted text -

- Show quoted text -

Thanks. I see. I'll try an Edit|Replace on the apostrophes.

In order to avoid printing empty rows or just to hide them temporarily
I use this macro "Squeeze_Lines".

Mark the area with the unwanted rows and then start the macro. All
rows in the selection with data will remain visible. The rest will be
grouped and collapsed. Formulas with result=0 will be hidden too.

Public Declare Function GetAsyncKeyState Lib "user32.dll" (ByVal vKey
As Long) As Integer

Sub Squeeze_Lines()
Dim i As Integer
Dim e As Integer
Dim j As Integer
Dim f As Integer
Dim h As Integer
Dim n As Integer
Dim m As Integer
Dim d As Boolean
Dim s As Boolean
Dim sf As Boolean
Dim thin_rows As Boolean

If Key_pressed(vbKeyShift) Then
sf = True ' the shift key switches off the grouping, rows
will only be hidden
End If
If Key_pressed(vbKeyControl) Then
thin_rows = True
End If

If Not ActiveWorkbook Is Nothing Then
i = Selection.Row ' start row
e = Selection.Row + Selection.Rows.Count - 1
j = Selection.Column
f = Selection.Column + Selection.Columns.Count - 1

If e = Columns(j).EntireColumn.Rows.Count Then
e = LastCell(ActiveSheet).Row
End If

If f = Rows(i).EntireRow.Columns.Count Then
f = LastCell(ActiveSheet).Column
End If

If (e - i) * (f - j) 100000 Then
If MsgBox("You've selected more than 100,000 cells." & Chr
(10) & _
"Please be patient or cancel now.", vbOKCancel)
= vbCancel Then
Exit Sub
Else
Application.ScreenUpdating = False
End If
End If

If Rows(i + 1).RowHeight = Rows(i).RowHeight Then
h = 3 ' if first 2 rows are equal height -squeeze
height is set = 3 points
Else ' otherwise height of the 2nd row is copied to other
empty rows
h = Rows(i + 1).RowHeight
End If
For n = i To e
d = False
For m = j To f
If IsError(Cells(n, m)) Then
d = True
s = False
Exit For
Else
If Cells(n, m).Value < Empty Then
d = True
s = False
Exit For
End If
End If
Next m
If s = True And d = False Then
If sf Then
Rows(n).EntireRow.Hidden = True
Else
Rows(n).Group
End If
ElseIf d = False Then
If thin_rows Then
Rows(n).RowHeight = h
Else
Rows(n).Group
End If
s = True
Else
s = False
End If
Next n
If Not sf Then ActiveSheet.Outline.ShowLevels RowLevels:=1
End If
End Sub

Function Key_pressed(key_to_check As Long) As Boolean
If GetAsyncKeyState(key_to_check) And &H8000 Then
Key_pressed = True
Else
Key_pressed = False
End If
End Function