Problem selecting a range to print...
 

I'm trying to get Excel XP to print a range of cells in a spreadshee
via a macro and I'm having problems selecting the range to prin
properly.

The range varies in the length based upon an advanced filter fro
another section of the spreadsheet that copies data to the area I wan
to print.

I created the following Macro to select the columns from M to V an
when I get the print preview, it ALWAYS makes it two pages even thoug
there is only one page of data. For some reason it goes does to ro
84 all the time even though there is nothing below row 37. This las
row is variable from 22 to 70 based upon the data from the copie
advanced filter.

Here's the Macro I made to print the area I'm interested in.

Sub Print_Estimate()
Application.ScreenUpdating = False
Columns("M:V").Select
ActiveSheet.PageSetup.PrintArea = "$M:$V"
With ActiveSheet.PageSetup
.PrintTitleRows = "$17:$18"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$M:$V"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&F"
.RightFooter = "Page &P of &N"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 8
.PrintErrors = xlPrintErrorsDisplayed
End With
Application.ScreenUpdating = True
ActiveWindow.SelectedSheets.PrintPreview
End Sub

I tried working around this by making Excel select just the range o
cells by using the CTRL-SHIFT-ARROW select method via a recorded macro
but the macro does not run the same as I recorded it. It only select
about the first 7 rows of the area I want instead of the correc
number.

Sub Select_Range()
Range("M1:V1").Select
Range("V1").Activate
Range(Selection, Selection.End(xlDown)).Select
End Sub

There are probably many ways around this problem, but I'm stumped!
Thanks in advance.

Mar

--
Message posted from http://www.ExcelForum.com

Problem selecting a range to print...
 

If you don't have blank rows internal to your data then:

Sub Print_Estimate()
Application.ScreenUpdating = False
Columns("M:V").Select
ActiveSheet.PageSetup.PrintArea = Range("M1"). _
CurrentRegion.Resize(,10).Address(external:=True)
With ActiveSheet.PageSetup
PrintTitleRows = "$17:$18"
PrintTitleColumns = ""
End With
With ActiveSheet.PageSetup
LeftHeader = ""
CenterHeader = ""
RightHeader = ""
LeftFooter = ""
CenterFooter = "&F"
RightFooter = "Page &P of &N"
LeftMargin = Application.InchesToPoints(0.25)
RightMargin = Application.InchesToPoints(0.25)
TopMargin = Application.InchesToPoints(0.25)
BottomMargin = Application.InchesToPoints(0.5)
HeaderMargin = Application.InchesToPoints(0)
FooterMargin = Application.InchesToPoints(0)
PrintHeadings = False
PrintGridlines = False
PrintComments = xlPrintNoComments
PrintQuality = 600
CenterHorizontally = True
CenterVertically = False
Orientation = xlPortrait
Draft = False
PaperSize = xlPaperLetter
FirstPageNumber = xlAutomatic
Order = xlDownThenOver
BlackAndWhite = False
Zoom = False
FitToPagesWide = 1
FitToPagesTall = 8
PrintErrors = xlPrintErrorsDisplayed
End With
Application.ScreenUpdating = True
ActiveWindow.SelectedSheets.PrintPreview
End Sub

--
Regards,
Tom Ogilvy

"usa1 " wrote in message
...
I'm trying to get Excel XP to print a range of cells in a spreadsheet
via a macro and I'm having problems selecting the range to print
properly.

The range varies in the length based upon an advanced filter from
another section of the spreadsheet that copies data to the area I want
to print.

I created the following Macro to select the columns from M to V and
when I get the print preview, it ALWAYS makes it two pages even though
there is only one page of data. For some reason it goes does to row
84 all the time even though there is nothing below row 37. This last
row is variable from 22 to 70 based upon the data from the copied
advanced filter.

Here's the Macro I made to print the area I'm interested in.

Sub Print_Estimate()
Application.ScreenUpdating = False
Columns("M:V").Select
ActiveSheet.PageSetup.PrintArea = "$M:$V"
With ActiveSheet.PageSetup
PrintTitleRows = "$17:$18"
PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$M:$V"
With ActiveSheet.PageSetup
LeftHeader = ""
CenterHeader = ""
RightHeader = ""
LeftFooter = ""
CenterFooter = "&F"
RightFooter = "Page &P of &N"
LeftMargin = Application.InchesToPoints(0.25)
RightMargin = Application.InchesToPoints(0.25)
TopMargin = Application.InchesToPoints(0.25)
BottomMargin = Application.InchesToPoints(0.5)
HeaderMargin = Application.InchesToPoints(0)
FooterMargin = Application.InchesToPoints(0)
PrintHeadings = False
PrintGridlines = False
PrintComments = xlPrintNoComments
PrintQuality = 600
CenterHorizontally = True
CenterVertically = False
Orientation = xlPortrait
Draft = False
PaperSize = xlPaperLetter
FirstPageNumber = xlAutomatic
Order = xlDownThenOver
BlackAndWhite = False
Zoom = False
FitToPagesWide = 1
FitToPagesTall = 8
PrintErrors = xlPrintErrorsDisplayed
End With
Application.ScreenUpdating = True
ActiveWindow.SelectedSheets.PrintPreview
End Sub

I tried working around this by making Excel select just the range of
cells by using the CTRL-SHIFT-ARROW select method via a recorded macro,
but the macro does not run the same as I recorded it. It only selects
about the first 7 rows of the area I want instead of the correct
number.

Sub Select_Range()
Range("M1:V1").Select
Range("V1").Activate
Range(Selection, Selection.End(xlDown)).Select
End Sub

There are probably many ways around this problem, but I'm stumped!
Thanks in advance.

Mark


---
Message posted from http://www.ExcelForum.com/

Problem selecting a range to print...
 

Thanks for the advice. It's didn't worked like I hoped, but it got me
thinking and I created the below macro that solved the problem.

In order to select the range I wanted, I needed to do the following.
It seems I needed to work around two different glitches or problems in
Excel since it did not work as expected. The below works
consistently.

Mark

Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Set r1 = Range("M1:V16")
Range("M17:V17").Select
Range(Selection, Selection.End(xlDown)).Select
Set r2 = Range(ActiveCell.CurrentRegion.Address)
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.Select
ActiveSheet.PageSetup.PrintArea = myMultiAreaRange


---
Message posted from http://www.ExcelForum.com/

Problem selecting a range to print...
 

if that works

r1 = range(Range("M1"),Cells(rows.count,13).End(xlup)). Resize(,10)
ActiveSheet.PageSetup.PrintArea = r1.Address(external:=True)

should work.

--
Regards,
Tom Ogilvy

"usa1 " wrote in message
...
Thanks for the advice. It's didn't worked like I hoped, but it got me
thinking and I created the below macro that solved the problem.

In order to select the range I wanted, I needed to do the following.
It seems I needed to work around two different glitches or problems in
Excel since it did not work as expected. The below works
consistently.

Mark

Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Set r1 = Range("M1:V16")
Range("M17:V17").Select
Range(Selection, Selection.End(xlDown)).Select
Set r2 = Range(ActiveCell.CurrentRegion.Address)
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.Select
ActiveSheet.PageSetup.PrintArea = myMultiAreaRange


---
Message posted from http://www.ExcelForum.com/