"Print This Page" Macro

[TinaF]

I had a spreadsheet template with columns as follows (and rows of
about 110 items):

Quantity Item Cost per Unit Hours per Unit Ext.
Cost Ext. Hours
Ground Mat/Grid 2.00 0.00
Ground Rod/Point 0.30 0.00

The Ext. Cost and Ext. Hours columns have formulas that will multiply
the quantity by either the cost per unit or hours per unit (or both).
There are also rows at the end of the list of items that do NOT
contain item names, cost per unit amounts or hours per unit amounts.
They only contain the extended cost and extend hours formulas. These
rows are for estimators to add unique items on the fly.

Estimators will go down the quantity column entering quantity numbers
and, if necessary, unique items with quantities at the end of the
list. Once they've filled in all their quantities I've set up a
"Filter Quantity" button that will filter the quantity column so that
only nonblanks show.

From here I need a "Print this Page" button with a macro that will

print the spreadsheet down to the last row that has a quantity/item
entered.

I've tried several times to record a macro with varying results.
Previously my macro would ignore (not print) any unique items that
were added in rows below the standard list. My re-recorded macro now
seems to be printing ALL the rows below the standard items even though
they are technically blank - i.e. they have no quantity, item name,
cost or hours filled in but they do have the cost ext. and hours ext.
formulas.

Help! It seems like it should be so simple.

Thanks,
Tina

 

[Gord Dibben]

Try this macro.

Sub Print_Visible()
Selection.SpecialCells(xlCellTypeVisible).Select
ActiveWindow.SelectedSheets.PrintPreview 'PrintOut
End Sub

Run it after you have enabled your Filter.


Gord Dibben MS Excel MVP

 

[TinaF]

Try this macro.

Sub Print_Visible()
Selection.SpecialCells(xlCellTypeVisible).Select
ActiveWindow.SelectedSheets.PrintPreview 'PrintOut
End Sub

Run it after you have enabled your Filter.

Gord Dibben MS Excel MVP

I tried the macro you suggested but it didn't do what I need. It took
me to the Print Preview screen, in which I had my filtered items and
all the "blank" rows below. So, I had a spreadsheet with three items
in the list and then 2 1/2 pages of empty cells printing out - 4 pages
of printout total, when I only really needed the first one.

 

[Gord Dibben]

I tested it on a sheet with 200 rows, filtered to show about 25 of them and
PrintOut printed just the visible rows and no other pages.

You can change the "PrintPreview to "PrintOut".

Just edit that last line.


Gord