Print question

D

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
 
D

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 ...
 
D

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top