conditional printing

[raymondsum]

Hi,

If Cell A1 = 1 ,Print Area (A2:M6)
If Cell A1 = 2, Print Area (A2:M11)
If Cell A1 = 3, Print Area(A2:M16) ... and so on

How do i write a vb to solve it.

Thanks in advance.

Raymond

Note : I wrongly post this question in tips and tutoria

 

[Patrick Molloy]

Sub SetPrintArea()
Select Case Range("A1")
Case 0
ActiveSheet.PageSetup.PrintArea = ""
Case 1
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$15"
Case 2
ActiveSheet.PageSetup.PrintArea = "$J$1:$K$15"
Case 3
Case Else
End Select

End Sub

Patrick Molloy
Microsoft Excel MVP

 

[raymondsum]

Patrick,

Thank you for your reply. It works.

Would you mind tell me how to write another vb to shorten the length of
programming because number of 'Case' will be more than 100.

Raymond

 

[Chip Pearson]

Raymond,

Try

ActiveSheet.PageSetup.PrintArea = _
"A2:" & Cells(1 + (Range("A1").Value * 5), "M").Address


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[raymondsum]

It works. Thank you very much.

Raymon