Have Range -> Put in Print Area?? via VBA

[want2b]

Sub yWant2b()
Range("G3").Select
ActiveCell.End(xlDown).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub

I've been happy all day cause I knew I could get this range into what I got from the micro recorder WRONG

How do I get Range(ActiveCell,ActiveCell.End(xlDown)).Select
To be the print Area???????

While I'm at it. I would like this to print on the left edge of the page,
It is only one column, The number or rows will varry but I would like it to print as large as possible or as small as necessary to get on one page.
I began to think I was getting a handle on this

Sorry But Thanks


ActiveSheet.PageSetup.PrintArea = ???????????????????
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0.2)
.TopMargin = Application.InchesToPoints(0.116666666666667)
.BottomMargin = Application.InchesToPoints(0.553472222222222)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 720
.CenterHorizontally = False
.CenterVertically = True
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 84
.PrintErrors = xlPrintErrorsDisplayed
End With

 

[Yogi Anand]

Hi Want2b:

Try ...

Sub yWant2bSetPrintArea()
    Set yRng = Range(Range("G3").End(xlDown), Range("G3").End(xlDown).End(xlDown))
    Set Print_Area = yRng
    ActiveWindow.SelectedSheets.PrintPreview
End Sub

I have used PrintPreview command in the macro -- so you can make all other necessary adjustments -- then you can replace PrintPreview command to actually print out.

I hope this helps!

 

[want2b]

Sub BlankSort()

Range("K3").Select
ActiveCell.Value = "=If(H3="""",0,H3)"
Selection.Copy
Range("K3:K" & Cells(Rows.count, 1).End(xlUp).Row).Select
ActiveSheet.Paste
Range("A3:K" & Cells(Rows.count, 1).End(xlUp).Row).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("K2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("K3:K" & Cells(Rows.count, 1).End(xlUp).Row).Clear
Set yRng = Range(Range("G3").End(xlDown), Range("G3").End(xlDown).End(xlDown))
Set Print_Area = yRng
ActiveWindow.SelectedSheets.PrintPreview

End Sub

This is how I used the code The Print range I get is from First filled Row in
G which is what I want But It goes over and starts at that row in A

And it continues to the last of G and I wanted it to stop at a Skip in G and Only do G

I tried Manually clearing the print range before running

It probably would work if I knew how to use it.

Thanks

 

[Yogi Anand]

Hi Want2b:

Did you try the code I posted? If you are setting the Print_Area for the range of entries in column G that we worked on earlier today -- then that part of the work is done. In the code I posted, I made use of the work that we had done earlier in setting the Print_Area.

Let us know how it goes -- Happy EXCELing!

 

[want2b]

That's what I was trying to do BUT I've Got something messed up.

Your code selected the range I need I just can't seem to get that into the print range.

 

[Yogi Anand]

Hi Want2b:

The code I posted in regard to your setting the Print_Area is ...

Sub yWant2bSetPrintArea()
    Set yRng = Range(Range("G3").End(xlDown), Range("G3").End(xlDown).End(xlDown))
    Set Print_Area = yRng
    ActiveWindow.SelectedSheets.PrintPreview
End Sub

This code does not select the Print_Area -- it sets the Print_Area. Try and let me know if it works for you!

 

[want2b]

Step-n through the code shows the proper range selected when yWant2b runs
then when yWant2bSetPrintArea runs the preview shows A(first row filled of G) through G(last filled row) Not stopping at the range yWant2b selected.

I believe I manually selected this range for printing last week.
I do three prints 1 the area selected by yWant2b
2 then I expand that range to include Column A
That expanded range is sorted on Column A and B
3 Then the range is expanded again to include the area
below (Columns A through G)
Any rows that are blank in G have been sorted to the top
and are not printed.

IF I manually clear print area before running I get the entire sheet in Preview.


yWant2b
yWant2bSetPrintArea

For now I'm just trying to print the column selected by yWant2b
Then I thought I'd worry about the other two

Sorry to be so much of a pain - Yesterday I was sure I was there when yWant2b selected the desired range.

Thanks

 

[Yogi Anand]

Hi Want2b:

Hope this helps ...

Sub setPrintAreaForARange1()
    
    '*************************************************
    'to select the range of interest
    Call yWant2b
    'or optionally use the following statement ...
    'Range("G3").Select
    'ActiveCell.End(xlDown).Select
    'Range(ActiveCell, ActiveCell.End(xlDown)).Select
    '**************************************************
    
    'now find the address of yRngBegin
    Set yRng = Selection
    Application.Goto yRng
    yRngBegin = ActiveCell.Address
    
    'now find the address of yRngEnd
    Application.Goto yRng.End(xlDown)
    yRngEnd = ActiveCell.Address
    
    'now set the PrintArea using yRngBegin and yRngEnd
    ActiveSheet.PageSetup.PrintArea = yRngBegin & ":" & yRngEnd
    ActiveWindow.SelectedSheets.PrintPreview
End Sub

Let us know if this helps!

 

[Yogi Anand]

Or, you may want to select the range and then use the selection. address to set the Print_Area as in ...

Sub setPrintAreaForArange2()
    '*************************************************
    'to select the range of interest
    '
    Range("G3").Select
    ActiveCell.End(xlDown).Select
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
    '
    'or optionally use the following statement ...
    'Call yWant2b
    '**************************************************
    ActiveSheet.PageSetup.PrintArea = Selection.Address
    ActiveWindow.SelectedSheets.PrintPreview
End Sub

 

[want2b]

WOW!!!!
I used your last method as I saw that first. And got it to work.
Even printed it out.

I have no idea which method is better. I'll probably try your first method later. But for now I'll have to wait until I can get my feet back on the ground.

I'm sure glad you have a big hammer to pound this into my head.

When 123 macroes finially clicked for me I was able to do almost anything you could think of...

With this I'm having trouble doing almost everything.

It's really nice for you to lead me by the hand especially now that you've seen how dense I really am. Your comments have really helped.

I don't even know how to thank you.