Macros - Only Print Rows With Data Plus Header

[Laurinda]

Hi,
I'm trying to update a macros for a print button. I'm trying to only print a row if there is data in column J. This macros is working great for that, but I also have a header that I would like printed (rows 1 to 15) which includes a picture of the logo. Does anyone know how I could update this macros to include the header with picture in the print? Thanks in advance!

Sub Button7_Click()
'prints rows of data, will not print rows if column J is blank
Application.ScreenUpdating = False
Range("J:J").EntireRow.Hidden = False
Range("J:J").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Range("J:J").EntireRow.Hidden = False
Application.ScreenUpdating = True
End Sub

 

[Redwolfx]

Try:
Untested

Sub Button7_Click()
'prints rows of data, will not print rows if column J is blank
Application.ScreenUpdating = False
Range("J:J").EntireRow.Hidden = False
Range("J16:J1000").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Range("J:J").EntireRow.Hidden = False
Application.ScreenUpdating = True
End Sub

 

[JoeMo]

This will set your print titles and print area as you have described them in your post and then print out.

Sub Button7_Click()
Dim lR As Long
lR = Range("J" & Rows.Count).End(xlUp).Row
Intersect(Rows("16:" & lR), ActiveSheet.UsedRange).Name = "Print_Area"
Rows("1:15").Name = "Print_Titles"
Application.ScreenUpdating = False
Range("J16:J" & lR).EntireRow.Hidden = False
Range("J16:J" & lR).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Range("J:J").EntireRow.Hidden = False
Application.ScreenUpdating = True
End Sub

 

[Laurinda]

This works great! Thank you very much! I am coming across another problem though... This excel spreadsheet is protected & the macros works great if it's not protected, but as soon as I protect it, it gives me the following error:

Run-time error '1004'"
Unable to se the Hidden property of the Range class

Do you know how to get around this?

 

[Redwolfx]

You need to unprotect the sheet then reprotect it

At the beginning put

ActiveSheet.Unprotect "Insert Password Here"

And at the End Put

ActiveSheet.Protect "Insert Password Here"

 

[Laurinda]

Thank you so much! Works perfectly!