Print area based on item list
 

I have an item list on on my first work sheet and several other worksheets
with invoices based on the first worksheet. How can I get the invoices to
print but only print the item lines with a greater than zero quantity?
--
Peter J

Print area based on item list
 

Hi Peter

You can loop like this to Hide_Print_Unhide or use AutoFilter
This example look in the cells Sheets("Sheet1").Range("A1:A30")

Sub Hide_Print_Unhide()
Dim cell As Range
Dim rng As Range

Application.ScreenUpdating = False

With Sheets("Sheet1")
Set rng = .Range("A1:A30")

For Each cell In rng
If .Cells(cell.Row, 1).Value = 0 Then _
.Rows(cell.Row).Hidden = True
Next cell
.PrintOut ' for testing use .PrintPreview
rng.EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Peter J" wrote in message ...
I have an item list on on my first work sheet and several other worksheets
with invoices based on the first worksheet. How can I get the invoices to
print but only print the item lines with a greater than zero quantity?
--
Peter J

Print area based on item list
 

Hi ron

I tried it and no result. I'll try to explain what i want again. I have an
invoice design on a worksheet. Column A is Quantity "Col B"= Size "Col
C"=unit price and "Col D"= Total.
Rows 7:108 are all item lines. I need that when an Item line has a quantity
of zero the line does not print
I am no good at programming so walk me through it
--
Peter J


"Ron de Bruin" wrote:

Hi Peter

You can loop like this to Hide_Print_Unhide or use AutoFilter
This example look in the cells Sheets("Sheet1").Range("A1:A30")

Sub Hide_Print_Unhide()
Dim cell As Range
Dim rng As Range

Application.ScreenUpdating = False

With Sheets("Sheet1")
Set rng = .Range("A1:A30")

For Each cell In rng
If .Cells(cell.Row, 1).Value = 0 Then _
.Rows(cell.Row).Hidden = True
Next cell
.PrintOut ' for testing use .PrintPreview
rng.EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Peter J" wrote in message ...
I have an item list on on my first work sheet and several other worksheets
with invoices based on the first worksheet. How can I get the invoices to
print but only print the item lines with a greater than zero quantity?
--
Peter J

Print area based on item list
 

Hi peter

This is working if the cells display a zero 0
I they are empty you can use
If cell.Value = "" Then _

Note the sheet name is "Sheet1"


Sub Hide_Print_Unhide_2()
Dim cell As Range
Dim rng As Range

Application.ScreenUpdating = False

With Sheets("Sheet1")
Set rng = .Range("A7:A108")

For Each cell In rng
If cell.Value = 0 Then _
.Rows(cell.Row).Hidden = True
Next cell

.PrintOut ' for testing use .PrintPreview
rng.EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Peter J" wrote in message ...
Hi ron

I tried it and no result. I'll try to explain what i want again. I have an
invoice design on a worksheet. Column A is Quantity "Col B"= Size "Col
C"=unit price and "Col D"= Total.
Rows 7:108 are all item lines. I need that when an Item line has a quantity
of zero the line does not print
I am no good at programming so walk me through it
--
Peter J


"Ron de Bruin" wrote:

Hi Peter

You can loop like this to Hide_Print_Unhide or use AutoFilter
This example look in the cells Sheets("Sheet1").Range("A1:A30")

Sub Hide_Print_Unhide()
Dim cell As Range
Dim rng As Range

Application.ScreenUpdating = False

With Sheets("Sheet1")
Set rng = .Range("A1:A30")

For Each cell In rng
If .Cells(cell.Row, 1).Value = 0 Then _
.Rows(cell.Row).Hidden = True
Next cell
.PrintOut ' for testing use .PrintPreview
rng.EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Peter J" wrote in message ...
I have an item list on on my first work sheet and several other worksheets
with invoices based on the first worksheet. How can I get the invoices to
print but only print the item lines with a greater than zero quantity?
--
Peter J

Print area based on item list
 

Hi Ron

Work great. Thanks alot.

Perhaps you can help me with the next arising problem which is I need all
the info which is at the bottom of the invoice ( Starting at row 40) to
remain there and not move up and down the page when the number of printable
items changes.

--
Peter J

Print area based on item list
 

You can make the text in the rows the same color as the backround
This example make the text white and print and make them black again

Sub Hide_Print_Unhide_3()
Dim cell As Range
Dim rng As Range

Application.ScreenUpdating = False

With Sheets("Sheet1")
Set rng = .Range("A7:A108")

For Each cell In rng
If cell.Value = 0 Then _
.Rows(cell.Row).Font.ColorIndex = 2
Next cell

.PrintOut ' for testing use .PrintPreview
rng.EntireRow.Font.ColorIndex = 1
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Peter J" wrote in message ...
Hi Ron

Work great. Thanks alot.

Perhaps you can help me with the next arising problem which is I need all
the info which is at the bottom of the invoice ( Starting at row 40) to
remain there and not move up and down the page when the number of printable
items changes.

--
Peter J

Print area based on item list
 

Hey Ron

I tried it but all it did was made the invoice the length of the number of
items. There are 102 Items(A7:A108) but the area available to print these
items per page is from A7:A40. If there are more items than that it can
continue on another page but if there are only 2 printable items then I need
the totals and messages at the bottom to print at the bottom and not in the
middle of the page
--
Peter J

Print area based on item list
 

Hi Peter

I look at your problem tomorrow


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Peter J" wrote in message ...
Hey Ron

I tried it but all it did was made the invoice the length of the number of
items. There are 102 Items(A7:A108) but the area available to print these
items per page is from A7:A40. If there are more items than that it can
continue on another page but if there are only 2 printable items then I need
the totals and messages at the bottom to print at the bottom and not in the
middle of the page
--
Peter J

Print area based on item list
 

Sorry forgot this thread

Try to find a solution for you fast

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
Hi Peter

I look at your problem tomorrow


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Peter J" wrote in message ...
Hey Ron

I tried it but all it did was made the invoice the length of the number of
items. There are 102 Items(A7:A108) but the area available to print these
items per page is from A7:A40. If there are more items than that it can
continue on another page but if there are only 2 printable items then I need
the totals and messages at the bottom to print at the bottom and not in the
middle of the page
--
Peter J

Print area based on item list
 

I will wait on your answer. Thanks alot
--
Peter J

Print area based on item list
 

Hi Peter

I not have a very good solution for you on this moment and no time to spend much time on it to try/test.

Start a new thread with this question
I am sure others have a already made solution for you


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Peter J" wrote in message ...
I will wait on your answer. Thanks alot
--
Peter J