Microsoft Excel 2003

I have a document that has formulas in every cell. However, only some cells
end up with visible text in them depending on the information that is pulled
into the spreadsheet.

Is there code that I can use to create a toolbar button that will select and
print only the area that contains the text and not the empty cells with
formulas?
--
Debra Ann

On Apr 8, 12:34*pm, Debra Ann
wrote:
Microsoft Excel 2003

I have a document that has formulas in every cell. However, only some cells
end up with visible text in them depending on the information that is pulled
into the spreadsheet. *

Is there code that I can use to create a toolbar button that will select and
print only the area that contains the text and not the empty cells with
formulas?
--
Debra Ann

Yes, there's a way - here's how I might do it:

1) In an unused column outside of the print area, paste-special values
of the cells you're checking that may or may not be empty.
(concatenate if you need to)
2) loop through all these values, if the value is null, hide the row
3) print
4) unhide all the rows that were hidden
5) clear the paste-special values

Anyone else have a better idea? (10$ says someone does :P )

HTH
Chris

Chris,

Thanks for you help. I got further clarification from the gentlemen that
needs help. He is actually looking for what the VBA code would be. He is
only familiar with VB and not VBA. Here is the email he just sent me:

Hi Deb:

Thanks a lot.

Here is some clarification of what I am looking for:

I know the columns and rows I want to print. Lets say they span the area
from Cell B10 to Cell Z3000, and all cells are of interest. Isnt there a
simple way to define that area with a VBA instruction?

The procedure Chris is talking about is really for skipping rows. Even
then, I would need the VBA instruction on how to hide a row, if no other
option is available.

Thanks a lot.

John H.

--
Debra Ann


"cht13er" wrote:

On Apr 8, 12:34 pm, Debra Ann
wrote:
Microsoft Excel 2003

I have a document that has formulas in every cell. However, only some cells
end up with visible text in them depending on the information that is pulled
into the spreadsheet.

Is there code that I can use to create a toolbar button that will select and
print only the area that contains the text and not the empty cells with
formulas?
--
Debra Ann

Yes, there's a way - here's how I might do it:

1) In an unused column outside of the print area, paste-special values
of the cells you're checking that may or may not be empty.
(concatenate if you need to)
2) loop through all these values, if the value is null, hide the row
3) print
4) unhide all the rows that were hidden
5) clear the paste-special values

Anyone else have a better idea? (10$ says someone does :P )

HTH
Chris

On Apr 9, 9:39 am, Debra Ann
wrote:
Chris,

Thanks for you help. I got further clarification from the gentlemen that
needs help. He is actually looking for what the VBA code would be. He is
only familiar with VB and not VBA. Here is the email he just sent me:

Hi Deb:

Thanks a lot.

Here is some clarification of what I am looking for:

I know the columns and rows I want to print. Let's say they span the area
from Cell B10 to Cell Z3000, and all cells are of interest. Isn't there a
simple way to define that area with a VBA instruction?

The procedure Chris is talking about is really for skipping rows. Even
then, I would need the VBA instruction on how to hide a row, if no other
option is available.

Thanks a lot.

John H.

--
Debra Ann



"cht13er" wrote:
On Apr 8, 12:34 pm, Debra Ann
wrote:
Microsoft Excel 2003

I have a document that has formulas in every cell. However, only some cells
end up with visible text in them depending on the information that is pulled
into the spreadsheet.

Is there code that I can use to create a toolbar button that will select and
print only the area that contains the text and not the empty cells with
formulas?
--
Debra Ann

Yes, there's a way - here's how I might do it:

1) In an unused column outside of the print area, paste-special values
of the cells you're checking that may or may not be empty.
(concatenate if you need to)
2) loop through all these values, if the value is null, hide the row
3) print
4) unhide all the rows that were hidden
5) clear the paste-special values

Anyone else have a better idea? (10$ says someone does :P )

HTH
Chris- Hide quoted text -

- Show quoted text -

Debra,

Dim r As Range
Set r = Range("b10:z3000")

or

ActiveSheet.PageSetup.PrintArea = "$B$10:$Z$3000"

or

to hide rows:
for iCounter = 1 to 3000
if len(cells(icounter,4))=0 Then
Rows(icounter).Select
Selection.EntireRow.Hidden = True
end if
next icounter


HTH

Chris