Print question

[David]

Need a Before_Print code that will do the following:
1) Maintain 3 header rows set in "rows to repeat at top"
2) Hide rows that do not contain dates for month in A2, dates start at C4
and go down the column, some of which may not have dates entered yet
3) Print the sheet
4) Unhide the rows previously hidden in 2)

something like
If month(c4:c124)<> month(a2) then entirerow.hidden=true
Range(visiblerows).printout
Range(hiddenrows).visible=true

--
David

[David]

David wrote

> something like
> If month(c4:c124)<> month(a2) then entirerow.hidden=true
> Range(visiblerows).printout
> Range(hiddenrows).visible=true

more like
If month(c4:c124)<> month(a2) OR isempty(cell in range(c4:c124) then ...

--
David

[David]

Original post seems to have disappeared
Need to filter rows before printing, hiding rows that aren't in desired
month or don't have desired date entries in range.

Borrowing from another file I got half of what I want:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
On Error GoTo Quit
Range("D4:C" & Range("Print_Area").Rows.Count).SpecialCells _
(xlCellTypeBlanks).EntireRow.Hidden = True
'<-- Need another line here for handling needed month
Application.OnTime Now(), ThisWorkbook.Name & "!WorkbookAfterPrint"
Quit:
End Sub

Sub WorkbookAfterPrint()
Range("C4:C" & Range("Print_Area").Rows.Count).SpecialCells _
(xlCellTypeBlanks).EntireRow.Hidden = False
'<-- Need another line here for handling needed month
End Sub

I do have a conditional formatting formula set and think it could be
adapted to look at the desired range, but need help with VBA syntax:
=AND(YEAR(D4)=YEAR($A$2),MONTH(D4)=MONTH($A$2))
IOW, how to adapt the formula to VBA

--
David

[David]

David wrote

> Original post seems to have disappeared
> Need to filter rows before printing, hiding rows that aren't in desired
> month or don't have desired date entries in range.
>
> Borrowing from another file I got half of what I want:
>
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> On Error GoTo Quit
> Range("D4:C" & Range("Print_Area").Rows.Count).SpecialCells _
> (xlCellTypeBlanks).EntireRow.Hidden = True
> '<-- Need another line here for handling needed month
> Application.OnTime Now(), ThisWorkbook.Name & "!WorkbookAfterPrint"
> Quit:
> End Sub
>
> Sub WorkbookAfterPrint()
> Range("C4:C" & Range("Print_Area").Rows.Count).SpecialCells _
> (xlCellTypeBlanks).EntireRow.Hidden = False
> '<-- Need another line here for handling needed month
> End Sub
>
> I do have a conditional formatting formula set and think it could be
> adapted to look at the desired range, but need help with VBA syntax:
> =AND(YEAR(D4)=YEAR($A$2),MONTH(D4)=MONTH($A$2))
> IOW, how to adapt the formula to VBA
>

Ok, after hours and hours I came to this solution:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
On Error GoTo Quit
Range("C4:C" & Range("Print_Area").Rows.Count).SpecialCells _
(xlCellTypeBlanks).EntireRow.Hidden = True
For Each cell In Range(Range("d4"), _
Range("d4").End(xlDown)).SpecialCells (xlCellTypeVisible)
If Month(cell) <> Month([a2]) Or Year(cell) <> Year([a2]) Then
cell.EntireRow.Hidden = True
End If
Next
Application.OnTime Now(), ThisWorkbook.Name & "!WorkbookAfterPrint"
Quit:
End Sub

Sub WorkbookAfterPrint()
Cells.EntireRow.Hidden = False
End Sub
--
David