Printing a specific area containing data and not formulas
 

I have a similar situation, but I am limited to not using macros as the
spreadsheet is to be sent to customers and we don't want to force the
customer to enable macros....

Is there any way to tell Excel not to print cells that contain formulas but
don't resolve to a value or otherwise resolve to an empty string (i.e. "") or
a blank? Perhaps there is a special character code that the formula could
put in the cells that would prevent them from printing just because they
contain formulas?

Any advice would be greatly appreciated!


"Ken Wright" wrote:

Am I right in thinking that you perhaps have input cells in say Cols A:E or so,
and then some formula cells in say Col F. In Col F you have copied the formula
down quite some way such that as you add data to the cells in Cols A:E then a
value will pop up in Col F for those rows.

If I am correct in that assumption, then assuming you can rely on for example
Col A to only contain data in the rows you wish to print (No formulas returning
blanks etc), then something like the following should work for you:-

Sub PrintData()

Dim LastRw As Long
Dim ColW As Long
Dim Rng As Range

With ActiveSheet
LastRw = .Cells(Rows.Count, "A").End(xlUp).Row
ColW = .UsedRange.Column - 1 + .UsedRange.Columns.Count
Set Rng = .Range(Cells(1, "A"), Cells(LastRw, ColW))

.PageSetup.PrintArea = Rng.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End With

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Jo-Anne" wrote in message
...
Good Day All,

I have a spreadsheet where a number of formulas exist, with no results in
them as yet. Because I have the format set to Acct, $ the rows show up as $-.

I don't want these to print, I only want to print up to and including the
last line with a formula result. I also need it to be dynamic, such that if
data is input into the next row down, the print range will adjust accordingly
- any suggestions ??

Thanks Much


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.736 / Virus Database: 490 - Release Date: 09/08/2004

Printing a specific area containing data and not formulas
 

You can make the cells white (background color)

Copy/Paste this event in the Thisworkbook module

Where do I paste the code that I want to use in my workbook
http://www.rondebruin.nl/code.htm


Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
.Cells.SpecialCells(xlCellTypeFormulas).Font.Color Index = 2
.PrintOut
.Cells.SpecialCells(xlCellTypeFormulas).Font.Color Index = 1
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub


More info here
http://www.rondebruin.nl/print.htm





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LynchInOKC" wrote in message ...
I have a similar situation, but I am limited to not using macros as the
spreadsheet is to be sent to customers and we don't want to force the
customer to enable macros....

Is there any way to tell Excel not to print cells that contain formulas but
don't resolve to a value or otherwise resolve to an empty string (i.e. "") or
a blank? Perhaps there is a special character code that the formula could
put in the cells that would prevent them from printing just because they
contain formulas?

Any advice would be greatly appreciated!


"Ken Wright" wrote:

Am I right in thinking that you perhaps have input cells in say Cols A:E or so,
and then some formula cells in say Col F. In Col F you have copied the formula
down quite some way such that as you add data to the cells in Cols A:E then a
value will pop up in Col F for those rows.

If I am correct in that assumption, then assuming you can rely on for example
Col A to only contain data in the rows you wish to print (No formulas returning
blanks etc), then something like the following should work for you:-

Sub PrintData()

Dim LastRw As Long
Dim ColW As Long
Dim Rng As Range

With ActiveSheet
LastRw = .Cells(Rows.Count, "A").End(xlUp).Row
ColW = .UsedRange.Column - 1 + .UsedRange.Columns.Count
Set Rng = .Range(Cells(1, "A"), Cells(LastRw, ColW))

.PageSetup.PrintArea = Rng.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End With

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Jo-Anne" wrote in message
...
Good Day All,

I have a spreadsheet where a number of formulas exist, with no results in
them as yet. Because I have the format set to Acct, $ the rows show up as $-.

I don't want these to print, I only want to print up to and including the
last line with a formula result. I also need it to be dynamic, such that if
data is input into the next row down, the print range will adjust accordingly
- any suggestions ??

Thanks Much


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.736 / Virus Database: 490 - Release Date: 09/08/2004

Printing a specific area containing data and not formulas
 

Thanks for the info, but as I mentioned I am limited to formulas only.
Although I know there are a lot of things I can accomplish using VBA, I do
not want to impose the use of macros on our customers. I would like to do
this entirely using basic spreadsheet options and/or cell functions...

Any other ideas would be greatly appreciated. Thanks.


"Ron de Bruin" wrote:

You can make the cells white (background color)

Copy/Paste this event in the Thisworkbook module

Where do I paste the code that I want to use in my workbook
http://www.rondebruin.nl/code.htm


Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
.Cells.SpecialCells(xlCellTypeFormulas).Font.Color Index = 2
.PrintOut
.Cells.SpecialCells(xlCellTypeFormulas).Font.Color Index = 1
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub


More info here
http://www.rondebruin.nl/print.htm





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LynchInOKC" wrote in message ...
I have a similar situation, but I am limited to not using macros as the
spreadsheet is to be sent to customers and we don't want to force the
customer to enable macros....

Is there any way to tell Excel not to print cells that contain formulas but
don't resolve to a value or otherwise resolve to an empty string (i.e. "") or
a blank? Perhaps there is a special character code that the formula could
put in the cells that would prevent them from printing just because they
contain formulas?

Any advice would be greatly appreciated!


"Ken Wright" wrote:

Am I right in thinking that you perhaps have input cells in say Cols A:E or so,
and then some formula cells in say Col F. In Col F you have copied the formula
down quite some way such that as you add data to the cells in Cols A:E then a
value will pop up in Col F for those rows.

If I am correct in that assumption, then assuming you can rely on for example
Col A to only contain data in the rows you wish to print (No formulas returning
blanks etc), then something like the following should work for you:-

Sub PrintData()

Dim LastRw As Long
Dim ColW As Long
Dim Rng As Range

With ActiveSheet
LastRw = .Cells(Rows.Count, "A").End(xlUp).Row
ColW = .UsedRange.Column - 1 + .UsedRange.Columns.Count
Set Rng = .Range(Cells(1, "A"), Cells(LastRw, ColW))

.PageSetup.PrintArea = Rng.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End With

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Jo-Anne" wrote in message
...
Good Day All,

I have a spreadsheet where a number of formulas exist, with no results in
them as yet. Because I have the format set to Acct, $ the rows show up as $-.

I don't want these to print, I only want to print up to and including the
last line with a formula result. I also need it to be dynamic, such that if
data is input into the next row down, the print range will adjust accordingly
- any suggestions ??

Thanks Much


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.736 / Virus Database: 490 - Release Date: 09/08/2004

Printing a specific area containing data and not formulas
 

Try this

Make the Font white with conditional formatting
http://www.contextures.com/xlCondFormat03.html#Print




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LynchInOKC" wrote in message ...
Thanks for the info, but as I mentioned I am limited to formulas only.
Although I know there are a lot of things I can accomplish using VBA, I do
not want to impose the use of macros on our customers. I would like to do
this entirely using basic spreadsheet options and/or cell functions...

Any other ideas would be greatly appreciated. Thanks.


"Ron de Bruin" wrote:

You can make the cells white (background color)

Copy/Paste this event in the Thisworkbook module

Where do I paste the code that I want to use in my workbook
http://www.rondebruin.nl/code.htm


Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
.Cells.SpecialCells(xlCellTypeFormulas).Font.Color Index = 2
.PrintOut
.Cells.SpecialCells(xlCellTypeFormulas).Font.Color Index = 1
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub


More info here
http://www.rondebruin.nl/print.htm





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LynchInOKC" wrote in message ...
I have a similar situation, but I am limited to not using macros as the
spreadsheet is to be sent to customers and we don't want to force the
customer to enable macros....

Is there any way to tell Excel not to print cells that contain formulas but
don't resolve to a value or otherwise resolve to an empty string (i.e. "") or
a blank? Perhaps there is a special character code that the formula could
put in the cells that would prevent them from printing just because they
contain formulas?

Any advice would be greatly appreciated!


"Ken Wright" wrote:

Am I right in thinking that you perhaps have input cells in say Cols A:E or so,
and then some formula cells in say Col F. In Col F you have copied the formula
down quite some way such that as you add data to the cells in Cols A:E then a
value will pop up in Col F for those rows.

If I am correct in that assumption, then assuming you can rely on for example
Col A to only contain data in the rows you wish to print (No formulas returning
blanks etc), then something like the following should work for you:-

Sub PrintData()

Dim LastRw As Long
Dim ColW As Long
Dim Rng As Range

With ActiveSheet
LastRw = .Cells(Rows.Count, "A").End(xlUp).Row
ColW = .UsedRange.Column - 1 + .UsedRange.Columns.Count
Set Rng = .Range(Cells(1, "A"), Cells(LastRw, ColW))

.PageSetup.PrintArea = Rng.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End With

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Jo-Anne" wrote in message
...
Good Day All,

I have a spreadsheet where a number of formulas exist, with no results in
them as yet. Because I have the format set to Acct, $ the rows show up as $-.

I don't want these to print, I only want to print up to and including the
last line with a formula result. I also need it to be dynamic, such that if
data is input into the next row down, the print range will adjust accordingly
- any suggestions ??

Thanks Much


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.736 / Virus Database: 490 - Release Date: 09/08/2004

Printing a specific area containing data and not formulas
 

Thanks again, but while this does prevent the contents of the cells from
printing it does not prevent Excel from printing extra blank pages for the
blank rows generated by formulas resulting in a blank cell...

Maybe I should attempt to explain my objective better...

I want to somehow prevent Excel from printing rows where the formulas result
in an empty or blank value. For example, lets say I have 200 rows where
columns A, B, C, & D all have the same formula. However, depending on the
calculations in these formulas perhaps only 50 rows result in an actual
value. The remaining 150 rows still contain formulas but they have no
"value" in that the formula yields a blank or "". However when I go to print
the spreadsheet, Excel still prints all 200 rows. I just want it to print
the 50 rows containing values...

Thanks again!



"Ron de Bruin" wrote:

Try this

Make the Font white with conditional formatting
http://www.contextures.com/xlCondFormat03.html#Print




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LynchInOKC" wrote in message ...
Thanks for the info, but as I mentioned I am limited to formulas only.
Although I know there are a lot of things I can accomplish using VBA, I do
not want to impose the use of macros on our customers. I would like to do
this entirely using basic spreadsheet options and/or cell functions...

Any other ideas would be greatly appreciated. Thanks.


"Ron de Bruin" wrote:

You can make the cells white (background color)

Copy/Paste this event in the Thisworkbook module

Where do I paste the code that I want to use in my workbook
http://www.rondebruin.nl/code.htm


Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
.Cells.SpecialCells(xlCellTypeFormulas).Font.Color Index = 2
.PrintOut
.Cells.SpecialCells(xlCellTypeFormulas).Font.Color Index = 1
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub


More info here
http://www.rondebruin.nl/print.htm





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LynchInOKC" wrote in message ...
I have a similar situation, but I am limited to not using macros as the
spreadsheet is to be sent to customers and we don't want to force the
customer to enable macros....

Is there any way to tell Excel not to print cells that contain formulas but
don't resolve to a value or otherwise resolve to an empty string (i.e. "") or
a blank? Perhaps there is a special character code that the formula could
put in the cells that would prevent them from printing just because they
contain formulas?

Any advice would be greatly appreciated!


"Ken Wright" wrote:

Am I right in thinking that you perhaps have input cells in say Cols A:E or so,
and then some formula cells in say Col F. In Col F you have copied the formula
down quite some way such that as you add data to the cells in Cols A:E then a
value will pop up in Col F for those rows.

If I am correct in that assumption, then assuming you can rely on for example
Col A to only contain data in the rows you wish to print (No formulas returning
blanks etc), then something like the following should work for you:-

Sub PrintData()

Dim LastRw As Long
Dim ColW As Long
Dim Rng As Range

With ActiveSheet
LastRw = .Cells(Rows.Count, "A").End(xlUp).Row
ColW = .UsedRange.Column - 1 + .UsedRange.Columns.Count
Set Rng = .Range(Cells(1, "A"), Cells(LastRw, ColW))

.PageSetup.PrintArea = Rng.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End With

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Jo-Anne" wrote in message
...
Good Day All,

I have a spreadsheet where a number of formulas exist, with no results in
them as yet. Because I have the format set to Acct, $ the rows show up as $-.

I don't want these to print, I only want to print up to and including the
last line with a formula result. I also need it to be dynamic, such that if
data is input into the next row down, the print range will adjust accordingly
- any suggestions ??

Thanks Much


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.736 / Virus Database: 490 - Release Date: 09/08/2004

Printing a specific area containing data and not formulas
 

Autofilter to show non-blanks only.
--
David Biddulph

"LynchInOKC" wrote in message
...
Thanks again, but while this does prevent the contents of the cells from
printing it does not prevent Excel from printing extra blank pages for the
blank rows generated by formulas resulting in a blank cell...

Maybe I should attempt to explain my objective better...

I want to somehow prevent Excel from printing rows where the formulas
result
in an empty or blank value. For example, lets say I have 200 rows where
columns A, B, C, & D all have the same formula. However, depending on the
calculations in these formulas perhaps only 50 rows result in an actual
value. The remaining 150 rows still contain formulas but they have no
"value" in that the formula yields a blank or "". However when I go to
print
the spreadsheet, Excel still prints all 200 rows. I just want it to print
the 50 rows containing values...

Thanks again!



"Ron de Bruin" wrote:

Try this

Make the Font white with conditional formatting
http://www.contextures.com/xlCondFormat03.html#Print




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LynchInOKC" wrote in message
...
Thanks for the info, but as I mentioned I am limited to formulas only.
Although I know there are a lot of things I can accomplish using VBA, I
do
not want to impose the use of macros on our customers. I would like to
do
this entirely using basic spreadsheet options and/or cell functions...

Any other ideas would be greatly appreciated. Thanks.


"Ron de Bruin" wrote:

You can make the cells white (background color)

Copy/Paste this event in the Thisworkbook module

Where do I paste the code that I want to use in my workbook
http://www.rondebruin.nl/code.htm


Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
.Cells.SpecialCells(xlCellTypeFormulas).Font.Color Index =
2
.PrintOut
.Cells.SpecialCells(xlCellTypeFormulas).Font.Color Index =
1
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub


More info here
http://www.rondebruin.nl/print.htm





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LynchInOKC" wrote in message
...
I have a similar situation, but I am limited to not using macros as
the
spreadsheet is to be sent to customers and we don't want to force
the
customer to enable macros....

Is there any way to tell Excel not to print cells that contain
formulas but
don't resolve to a value or otherwise resolve to an empty string
(i.e. "") or
a blank? Perhaps there is a special character code that the formula
could
put in the cells that would prevent them from printing just because
they
contain formulas?

Any advice would be greatly appreciated!


"Ken Wright" wrote:

Am I right in thinking that you perhaps have input cells in say
Cols A:E or so,
and then some formula cells in say Col F. In Col F you have copied
the formula
down quite some way such that as you add data to the cells in Cols
A:E then a
value will pop up in Col F for those rows.

If I am correct in that assumption, then assuming you can rely on
for example
Col A to only contain data in the rows you wish to print (No
formulas returning
blanks etc), then something like the following should work for
you:-

Sub PrintData()

Dim LastRw As Long
Dim ColW As Long
Dim Rng As Range

With ActiveSheet
LastRw = .Cells(Rows.Count, "A").End(xlUp).Row
ColW = .UsedRange.Column - 1 + .UsedRange.Columns.Count
Set Rng = .Range(Cells(1, "A"), Cells(LastRw, ColW))

.PageSetup.PrintArea = Rng.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End With

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Jo-Anne" wrote in message
...
Good Day All,

I have a spreadsheet where a number of formulas exist, with no
results in
them as yet. Because I have the format set to Acct, $ the rows
show up as $-.

I don't want these to print, I only want to print up to and
including the
last line with a formula result. I also need it to be dynamic,
such that if
data is input into the next row down, the print range will adjust
accordingly
- any suggestions ??

Thanks Much


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.736 / Virus Database: 490 - Release Date: 09/08/2004