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.
--
By persisting in your path, though you forfeit the little, you gain the
great.

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

--
Gary''s Student - gsnu200828


"DavidH56" wrote:

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.
--
By persisting in your path, though you forfeit the little, you gain the
great.

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
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200828


"DavidH56" wrote:

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.
--
By persisting in your path, though you forfeit the little, you gain the
great.

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" wrote:

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
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200828


"DavidH56" wrote:

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.
--
By persisting in your path, though you forfeit the little, you gain the
great.

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.
--
By persisting in your path, though you forfeit the little, you gain the
great.



"JLGWhiz" wrote:

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" wrote:

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
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200828


"DavidH56" wrote:

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.
--
By persisting in your path, though you forfeit the little, you gain the
great.

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

--
By persisting in your path, though you forfeit the little, you gain the
great.



"DavidH56" wrote:

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.
--
By persisting in your path, though you forfeit the little, you gain the
great.



"JLGWhiz" wrote:

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" wrote:

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
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200828


"DavidH56" wrote:

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.
--
By persisting in your path, though you forfeit the little, you gain the
great.