MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Just showin' off! (I got it Barrie & Cpod !!!)


Posted by Bill Robbins on November 07, 2001 10:58 AM

Hi Barrie and Cpod.

You both contributed to the solution I was finally able (with God's help) to come up with for my printing problem.

The following macro will scan 18 rows and check if there is anything in columns K:S to match up with A:J. If so, both ranges are printed on two pages. If not, only A:J is printed. Then the next 18 rows are processed the same way, and so on, down to the end of whatever has been entered on the worksheet according to column "A".

'Written by Bill Robbins
For x = 1 To pagecount
If rw <= lastpage Then
If Range("K" & rw) <> "" Then
flag = flag + 1
End If
rw = rw + 1
Next pg
If flag > 0 Then
ActiveSheet.PageSetup.PrintArea = "A" & rw - 18 & ":S" & rw - 1
ActiveSheet.PrintOut
flag = 0
Else
ActiveSheet.PageSetup.PrintArea = "A" & rw - 18 & ":J" & rw - 1
ActiveSheet.PrintOut
End If
Else
For pg = 1 To 18
If Range("K" & rw) <> "" Then
flag = flag + 1
End If
rw = rw + 1
Next pg
If flag > 0 Then
ActiveSheet.PageSetup.PrintArea = "A" & rw - 18 & ":S" & rw - 1
flag = 0
Else
ActiveSheet.PageSetup.PrintArea = "A" & rw - 18 & ":J" & rw - 1
End If
End If
Next x

End Sub

Thank you so much for your help and patience, and thank you "Mr. Excel.com" for having this site. Without the help and tips available here, I doubt I could have EVER come up with a solution to my printing problem for an important work related spreadsheet.

Best regards,

Bill Robbins.


Posted by Barrie Davidson on November 07, 2001 11:23 AM

Bet you fun doing it too ! (nt)

End Sub

Posted by Barrie Davidson on November 07, 2001 1:25 PM

Meant to say: Bet you had fun doing it too - time I pay attention to my typing (nt)

End Sub