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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

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

-----Original Message-----

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 tutorial


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

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" wrote in
message ...

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 tutorial


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

It works. Thank you very much.

Raymon

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements