Setting Print Area

[DavidH56]

Hello,

I am using Excel 2003. I would appreciate it greatly if someone could
assist me with a macro to set my print area. I have 17 columns (with 16
being visible) full of data and varying rows each time that my report is
created. I have sorted on column 3 (increasing) as this column contains any
number from 0 to 3000 or more. I would like the code to set and limit the
print area to rows to include integers 0 to 200. Anything >200 is not to be
included in the print area. I have a header also.

Thanks for any assistance that you can provide.

 

[Gary''s Student]

Sub Macro1()
For i = 1 To Rows.Count
If Cells(i, "G").Value > 200 Then
Exit For
End If
Next
i = i - 1
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$" & i
End Sub

This assumes that your 17 columns are columns A thru Q

 

[DavidH56]

Thanks for your quick response Gary's Student. I tried running the code I
used "C" instead of "G" because the integer would be in this column. I got a
runtime - unable to set printarea property to the pagesetup class and this
line was highlighted yellow:
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$" & i

 

[JLGWhiz]

I used the code that GS posted and made the change to column C. I filled
column C with values from 0 to greater than 200 and ran the code. It set a
print area for A1:Q201, which appears to be the correct range. No error
messages were displayed. Check your code to be sure you did not omit commas
or quiotation marks and that you did not inadvertantly add something that
should not be in there.

 

[DavidH56]

Thanks JLGWhiz for your input. I went back and tried it again ans still got
the error message. I then tweaked it just a little and this is the code that
worked for me.

Sub Macro1()
For i = 2 To Rows.Count
If Cells(i, "G").Value > 200 Then
Exit For
End If
Next
i = i - 1
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$" & i
End Sub

Again thank you GS for solving my problem.

 

[DavidH56]

Oops, Actually this is the correct code:

Sub Macro1()
For i = 2 To Rows.Count
If Cells(i, "C").Value > 200 Then
Exit For
End If
Next
i = i - 1
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$" & i
End Sub