I am trying to develp a macro that will set the Print Range in a worksheet. I
have tried to modify several macos on the forums with no success. The problem
is I need to count down to the bottom of column A (which does contain some
blanks), to the first row that contains 0. Then I want the macro to set the
Print Range to A1:FX, where X is equal to the last row before the 0.

Thanks in advance! (Using Excel 2003)

Try this macro...

Sub SetPrintArea()
Dim C As Range
With Worksheets(2)
Set C = .Range("A:A").Find(What:=0, LookIn:=xlValues, LookAt:=xlWhole)
If Not C Is Nothing Then
.PageSetup.PrintArea = "$A$1:$F$" & C.Offset(-1).Row
End If
End With
End Sub

Rick


"John" wrote in message
...
I am trying to develp a macro that will set the Print Range in a worksheet.
I
have tried to modify several macos on the forums with no success. The
problem
is I need to count down to the bottom of column A (which does contain some
blanks), to the first row that contains 0. Then I want the macro to set
the
Print Range to A1:FX, where X is equal to the last row before the 0.

Thanks in advance! (Using Excel 2003)

Nope...this is still setting the print area to far. It's including all the
rows in column A that contain 0.

"Rick Rothstein (MVP - VB)" wrote:

Try this macro...

Sub SetPrintArea()
Dim C As Range
With Worksheets(2)
Set C = .Range("A:A").Find(What:=0, LookIn:=xlValues, LookAt:=xlWhole)
If Not C Is Nothing Then
.PageSetup.PrintArea = "$A$1:$F$" & C.Offset(-1).Row
End If
End With
End Sub

Rick


"John" wrote in message
...
I am trying to develp a macro that will set the Print Range in a worksheet.
I
have tried to modify several macos on the forums with no success. The
problem
is I need to count down to the bottom of column A (which does contain some
blanks), to the first row that contains 0. Then I want the macro to set
the
Print Range to A1:FX, where X is equal to the last row before the 0.

Thanks in advance! (Using Excel 2003)

I don't see how it could possibly be doing that. Are you using the code
exactly as I posted it? All my tests show the code finding the first zero in
Column A (whether a number or text; whether typed in or the result of a
formula) and then setting the print area from A1 to the row in Colum F
immediately before it. Did you change my test Worksheets reference to the
worksheet where your data actually is?

Rick


"John" wrote in message
...
Nope...this is still setting the print area to far. It's including all the
rows in column A that contain 0.

"Rick Rothstein (MVP - VB)" wrote:

Try this macro...

Sub SetPrintArea()
Dim C As Range
With Worksheets(2)
Set C = .Range("A:A").Find(What:=0, LookIn:=xlValues,
LookAt:=xlWhole)
If Not C Is Nothing Then
.PageSetup.PrintArea = "$A$1:$F$" & C.Offset(-1).Row
End If
End With
End Sub

Rick


"John" wrote in message
...
I am trying to develp a macro that will set the Print Range in a
worksheet.
I
have tried to modify several macos on the forums with no success. The
problem
is I need to count down to the bottom of column A (which does contain
some
blanks), to the first row that contains 0. Then I want the macro to set
the
Print Range to A1:FX, where X is equal to the last row before the 0.

Thanks in advance! (Using Excel 2003)

'/========================================/
Sub Macro1()
Dim i As Long, lngLastRow As Long

Range("A1").Select

lngLastRow = _
ActiveSheet.UsedRange.Columns(1). _
SpecialCells(xlCellTypeLastCell).Row

For i = 1 To lngLastRow - 1
If ActiveCell.Offset(i, 0).Value2 = 0 And _
Not IsEmpty(ActiveCell.Offset(i, 0).Value2) Then
lngLastRow = ActiveCell.Offset(i, 0).Row - 1
Exit For
End If
Next i

ActiveSheet.PageSetup.PrintArea = "$A$1:$F$" & lngLastRow

End Sub
'/========================================/

--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"John" wrote:

I am trying to develp a macro that will set the Print Range in a worksheet. I
have tried to modify several macos on the forums with no success. The problem
is I need to count down to the bottom of column A (which does contain some
blanks), to the first row that contains 0. Then I want the macro to set the
Print Range to A1:FX, where X is equal to the last row before the 0.

Thanks in advance! (Using Excel 2003)

Rick

Yep, I changed the code to point to the sheet that I wanted it to set the
Print Range and no go. : (

"Rick Rothstein (MVP - VB)" wrote:

I don't see how it could possibly be doing that. Are you using the code
exactly as I posted it? All my tests show the code finding the first zero in
Column A (whether a number or text; whether typed in or the result of a
formula) and then setting the print area from A1 to the row in Colum F
immediately before it. Did you change my test Worksheets reference to the
worksheet where your data actually is?

Rick


"John" wrote in message
...
Nope...this is still setting the print area to far. It's including all the
rows in column A that contain 0.

"Rick Rothstein (MVP - VB)" wrote:

Try this macro...

Sub SetPrintArea()
Dim C As Range
With Worksheets(2)
Set C = .Range("A:A").Find(What:=0, LookIn:=xlValues,
LookAt:=xlWhole)
If Not C Is Nothing Then
.PageSetup.PrintArea = "$A$1:$F$" & C.Offset(-1).Row
End If
End With
End Sub

Rick


"John" wrote in message
...
I am trying to develp a macro that will set the Print Range in a
worksheet.
I
have tried to modify several macos on the forums with no success. The
problem
is I need to count down to the bottom of column A (which does contain
some
blanks), to the first row that contains 0. Then I want the macro to set
the
Print Range to A1:FX, where X is equal to the last row before the 0.

Thanks in advance! (Using Excel 2003)

This works perfectly, thanks!

"Gary Brown" wrote:

'/========================================/
Sub Macro1()
Dim i As Long, lngLastRow As Long

Range("A1").Select

lngLastRow = _
ActiveSheet.UsedRange.Columns(1). _
SpecialCells(xlCellTypeLastCell).Row

For i = 1 To lngLastRow - 1
If ActiveCell.Offset(i, 0).Value2 = 0 And _
Not IsEmpty(ActiveCell.Offset(i, 0).Value2) Then
lngLastRow = ActiveCell.Offset(i, 0).Row - 1
Exit For
End If
Next i

ActiveSheet.PageSetup.PrintArea = "$A$1:$F$" & lngLastRow

End Sub
'/========================================/

--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"John" wrote:

I am trying to develp a macro that will set the Print Range in a worksheet. I
have tried to modify several macos on the forums with no success. The problem
is I need to count down to the bottom of column A (which does contain some
blanks), to the first row that contains 0. Then I want the macro to set the
Print Range to A1:FX, where X is equal to the last row before the 0.

Thanks in advance! (Using Excel 2003)

Can you post your worksheet on line somewhere for us to download and look
at? While you will get the best possible answer if more than my eyes look at
it; but, if you want, you can send the worksheet directly to me (remove the
two NO.SPAM text entries from my address) and I will see if I can find out
why you are getting different results than I am with my code.

Rick


"John" wrote in message
...
Rick

Yep, I changed the code to point to the sheet that I wanted it to set the
Print Range and no go. : (

"Rick Rothstein (MVP - VB)" wrote:

I don't see how it could possibly be doing that. Are you using the code
exactly as I posted it? All my tests show the code finding the first zero
in
Column A (whether a number or text; whether typed in or the result of a
formula) and then setting the print area from A1 to the row in Colum F
immediately before it. Did you change my test Worksheets reference to the
worksheet where your data actually is?

Rick


"John" wrote in message
...
Nope...this is still setting the print area to far. It's including all
the
rows in column A that contain 0.

"Rick Rothstein (MVP - VB)" wrote:

Try this macro...

Sub SetPrintArea()
Dim C As Range
With Worksheets(2)
Set C = .Range("A:A").Find(What:=0, LookIn:=xlValues,
LookAt:=xlWhole)
If Not C Is Nothing Then
.PageSetup.PrintArea = "$A$1:$F$" & C.Offset(-1).Row
End If
End With
End Sub

Rick


"John" wrote in message
...
I am trying to develp a macro that will set the Print Range in a
worksheet.
I
have tried to modify several macos on the forums with no success.
The
problem
is I need to count down to the bottom of column A (which does
contain
some
blanks), to the first row that contains 0. Then I want the macro to
set
the
Print Range to A1:FX, where X is equal to the last row before the 0.

Thanks in advance! (Using Excel 2003)