[RAILhead]

OK, everyone at the office is stumped, so I thought I'd see if there were any Excel wizards here to help us figure something out: how in the living cr@p can you get a macro to recognize the selected cells and set them as the print area?

If I have A1:Z100 selected, I want my macro to set those cells as the print area. If I have B3:C3 selected, I want my macro to set those cells as the print area, and so on, for any range I select.

If you record the process, you get something like this:
ActiveSheet.PageSetup.PrintArea = "$A$1:$U$112" — which is wrong b/c the range is hard-coded.

We need it to say something like:
ActiveSheet.PageSetup.PrintArea = "MyDadgumRangeSelection"

Nothing we do works, so any assistance would be appreciated!

TIA,
Maury

[gorickey]

Will this work?

Start = Range("A1").Address
Finish = Range("P1").End(xlDown).Address

myPrintArea = Start & ":" & Finish

ActiveSheet.PageSetup.PrintArea = myPrintArea

[RAILhead]

Originally posted by gorickey:
Will this work?

Start = Range("A1").Address
Finish = Range("P1").End(xlDown).Address

myPrintArea = Start & ":" & Finish

ActiveSheet.PageSetup.PrintArea = myPrintArea

Almost, but it's stopping at the row I have set to repeat at the top of all pages. Here's the macro in full:

Sub PageSetup()
'
' PageSetup Macro
' Macro recorded 6/8/2004 by Maury McCown
'

Start = Range("A1").Address
Finish = Range("U1").End(xlDown).Address
myPrintArea = Start & ":" & Finish
ActiveSheet.PageSetup.PrintArea = myPrintArea

With ActiveSheet.PageSetup
.PrintTitleRows = "$6:$6"
.PrintTitleColumns = ""
End With

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -4
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
End Sub



Maury

[gorickey]

Anything in this "tip" help at all?

[RAILhead]

My wife, who works at the same company I do, came up with a solution:

Start = Range("A1").Address
Range("V1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
Selection.End(xlUp).Select
Finish = ActiveCell.Offset(1, 0).Address
myPrintArea = Start & ":" & Finish
ActiveSheet.PageSetup.PrintArea = myPrintArea

Since "V1" will always be empty, we can go to the last cell in that column, then move left one cell and up until you see a cell with content last cell in "U1"). For layout purposes, I then need to shift down 1 more cell, and the "Finish" cell is established.

I guess I'm cooking dinner tonight! Thanks for the macro ideas in your post, too, GR, as they've also been used to help with this.

Maury

[gorickey]

Smart lady you got there...

[RAILhead]

Originally posted by gorickey:
Smart lady you got there...

yes she is — and I forgot to explain why your code didn't work: there are blank cells in between data. Your method would only go as far as the last "full" cell, so the first empty one was stopping everything.

Thanks again,
Maury

[gorickey]

Originally posted by RAILhead:
[B]yes she is — and I forgot to explain why your code didn't work: there are blank cells in between data. Your method would only go as far as the last "full" cell, so the first empty one was stopping everything./B]

Aww, gotcha...that makes sense!