Print Range Question

[John]

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 Rothstein \(MVP - VB\)]

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]

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\)]

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

 

[Gary Brown]

'/========================================/
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
'/========================================/

 

[John]

Rick

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

 

[John]

This works perfectly, thanks!

'/========================================/
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
'/========================================/

 

[Rick Rothstein \(MVP - VB\)]

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