I want the capability of having multiple lines on the top of my worksheet,
but if a line is NOT going to be populated, then the row will be hidden,
preferably both while viewing as well as when printing, but at least when
printing.

Here is the scenario:
This is for monitoring invoices applied against purchase orders, each
worksheet is its own purchase order. Each purchase order is made up of 1 or
more line items.
Row 1 will have report header information, to only print on page 1.
Row 2 will have report total information, again to only print on page 1.
Rows 3-10 (currently) have room for up to 16 separate line items (line items
1 & 2 on row 3, line items 3 & 4 on row 4, etc.)
Row 11: Blank buffer line.
Rows 12-13 will have the headers for recording an invoice. If the document
spills to multiple pages, these are the ONLY headers I want on subsequent
pages.
Rows 14+ will have the detailed information for invoices applied to the
invoice.

I have some hidden columns that allows the worksheet to tabulate amount
applied to each line item, amount remaining on each line item, if a line item
was closed early, or modified to a new line item (including, hypothetically,
i have a line item that was originally set for $10,000.00, currently having
applied $2,000.00, we need to reduce the line item by $3,000.00 to free up
some budgeting for another purchase order, we actually have to close the
current line, and would open the remaining balance on a new line of
$5,000.00). All this works wonderfully well. Because the invoice rows 14+
have some columns with calculations, I am even using the Print Area with some
offsetting to ensure it prints only valid data, and not 'blank' cells that
still have formulas. (I have had no luck of the formulas always coming down
from the line above).

What I want (sorry to be long winded), is for Rows 4-10 to be able to be
'collapsed' when not in use. If I only have 1 or 2 line items, I don't want 7
rows of white space to be viewed (much less printed). I do have the worksheet
protected to ensure not writing over formulas, and it gets monotonous having
to unprotect, hide/unhide rows (or change row height), then reprotecting the
workbook.

Thanks in advance for any help that may be had.

--
John C

This code will unprotect the sheet, hide any rows that
do not have a value in column "A", then reprotect the
sheet. This is clearly not the whole solution, but it
might give you a good starting point.

Sub testing()

Dim lngRow As Long

ThisWorkbook.Worksheets("Sheet1").Protect _
DrawingObjects:=False, Contents:=False, Scenarios:=False
For lngRow = 3 To 10
If Range("A" & lngRow).Value = "" Then
ActiveSheet.Rows(lngRow).Hidden = True
End If
Next
ThisWorkbook.Worksheets("Sheet1").Protect _
DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

"John C" wrote:

I want the capability of having multiple lines on the top of my worksheet,
but if a line is NOT going to be populated, then the row will be hidden,
preferably both while viewing as well as when printing, but at least when
printing.

Here is the scenario:
This is for monitoring invoices applied against purchase orders, each
worksheet is its own purchase order. Each purchase order is made up of 1 or
more line items.
Row 1 will have report header information, to only print on page 1.
Row 2 will have report total information, again to only print on page 1.
Rows 3-10 (currently) have room for up to 16 separate line items (line items
1 & 2 on row 3, line items 3 & 4 on row 4, etc.)
Row 11: Blank buffer line.
Rows 12-13 will have the headers for recording an invoice. If the document
spills to multiple pages, these are the ONLY headers I want on subsequent
pages.
Rows 14+ will have the detailed information for invoices applied to the
invoice.

I have some hidden columns that allows the worksheet to tabulate amount
applied to each line item, amount remaining on each line item, if a line item
was closed early, or modified to a new line item (including, hypothetically,
i have a line item that was originally set for $10,000.00, currently having
applied $2,000.00, we need to reduce the line item by $3,000.00 to free up
some budgeting for another purchase order, we actually have to close the
current line, and would open the remaining balance on a new line of
$5,000.00). All this works wonderfully well. Because the invoice rows 14+
have some columns with calculations, I am even using the Print Area with some
offsetting to ensure it prints only valid data, and not 'blank' cells that
still have formulas. (I have had no luck of the formulas always coming down
from the line above).

What I want (sorry to be long winded), is for Rows 4-10 to be able to be
'collapsed' when not in use. If I only have 1 or 2 line items, I don't want 7
rows of white space to be viewed (much less printed). I do have the worksheet
protected to ensure not writing over formulas, and it gets monotonous having
to unprotect, hide/unhide rows (or change row height), then reprotecting the
workbook.

Thanks in advance for any help that may be had.

--
John C

Works like a charm! I have no problem setting a button to run the macro for
it. I do have to change the macro to make sure it selects the active
worksheet (not named sheet 1), but other than that, thank you kindly.
--
John C


"TomPl" wrote:

This code will unprotect the sheet, hide any rows that
do not have a value in column "A", then reprotect the
sheet. This is clearly not the whole solution, but it
might give you a good starting point.

Sub testing()

Dim lngRow As Long

ThisWorkbook.Worksheets("Sheet1").Protect _
DrawingObjects:=False, Contents:=False, Scenarios:=False
For lngRow = 3 To 10
If Range("A" & lngRow).Value = "" Then
ActiveSheet.Rows(lngRow).Hidden = True
End If
Next
ThisWorkbook.Worksheets("Sheet1").Protect _
DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

"John C" wrote:

I want the capability of having multiple lines on the top of my worksheet,
but if a line is NOT going to be populated, then the row will be hidden,
preferably both while viewing as well as when printing, but at least when
printing.

Here is the scenario:
This is for monitoring invoices applied against purchase orders, each
worksheet is its own purchase order. Each purchase order is made up of 1 or
more line items.
Row 1 will have report header information, to only print on page 1.
Row 2 will have report total information, again to only print on page 1.
Rows 3-10 (currently) have room for up to 16 separate line items (line items
1 & 2 on row 3, line items 3 & 4 on row 4, etc.)
Row 11: Blank buffer line.
Rows 12-13 will have the headers for recording an invoice. If the document
spills to multiple pages, these are the ONLY headers I want on subsequent
pages.
Rows 14+ will have the detailed information for invoices applied to the
invoice.

I have some hidden columns that allows the worksheet to tabulate amount
applied to each line item, amount remaining on each line item, if a line item
was closed early, or modified to a new line item (including, hypothetically,
i have a line item that was originally set for $10,000.00, currently having
applied $2,000.00, we need to reduce the line item by $3,000.00 to free up
some budgeting for another purchase order, we actually have to close the
current line, and would open the remaining balance on a new line of
$5,000.00). All this works wonderfully well. Because the invoice rows 14+
have some columns with calculations, I am even using the Print Area with some
offsetting to ensure it prints only valid data, and not 'blank' cells that
still have formulas. (I have had no luck of the formulas always coming down
from the line above).

What I want (sorry to be long winded), is for Rows 4-10 to be able to be
'collapsed' when not in use. If I only have 1 or 2 line items, I don't want 7
rows of white space to be viewed (much less printed). I do have the worksheet
protected to ensure not writing over formulas, and it gets monotonous having
to unprotect, hide/unhide rows (or change row height), then reprotecting the
workbook.

Thanks in advance for any help that may be had.

--
John C