print area defined

[up4asmile]

Hello, all,

I know there must be a way to do this, so I'm throwing it out to you.

I am trying to redefine a print area. It is different for every sheet. What I do know is that it will end in column L.

Can I do something similar to this? (This, of course, failed.) ActiveSheet.PageSetup.PrintArea = "$A$1:L1.End(xlDown).Select"

ActiveSheet.PageSetup.PrintArea = "$A$1:$L$147" That works and I am trying to make the end property a variable.

Any ideas?

[Demeter]

Hi,

would something like following help just from top of my head

Please Login or Register  to view this content.

You can build up a string of cariables by using the & and the "signs

example
dim somefigure as long
dim lastrow as long

"$A$" & somefigure & ":$L$" & lastrow

where somefigure and lastrow are variables set by you.

[up4asmile]

thanks, I'll give it a try;

No, it didn't work. It set the print area as A1 through L1, lol

[up4asmile]

I tried this and it almost worked:
Dim lastrow As Long

lastrow = ActiveSheet.Cells(Rows.Count, "H").End(xlDown).Row

ActiveSheet.PageSetup.PrintArea = "$A$1:$H$" & lastrow

except it selected all of the rows instead of just to the end of column H

[Demeter]

Can you post an example?

[up4asmile]

really any excel, any information, with the cells being filled from A1 through L ?.

In a previous process, I defined the print area for the first report that was produced.

Now I am adding different information and need to be able to redefine the print area. I have automated all of the other steps except redefining the print area. That one sure is tricky.

The coding above sure is close.

I appreciate your help!

I tried this, thinking the last part needed linking, but it didn't work:

Dim lastrow As Long

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlDown).Row

ActiveSheet.PageSetup.PrintArea = ("$A$1:$H$" & lastrow)

[Demeter]

What do you mean exactly with:
except it selected all of the rows instead of just to the end of column H ?

The code just searches for the last used row in column H and uses this as last row in your range.


In your code leave at the ()
and you use xldown this emans that you are searching for first cell from top, so if you have empty cell in between to cells it will not discover the second full cell (example you have A2 and A4 filles, you search from top you find A2 and not A4. when xlup, you will find A4.)

Please Login or Register  to view this content.

[up4asmile]

Thanks, Demeter, for taking a long look at this. I appreciate it.

When I use the code I posted it selects A:H as the print area, so it goes way past what I want.

There are no blank lines in the file (and never will be), so the xlDown should work. (Yes, I know about the blank lines possibility you mentioned)

I used the coding just as you showed above and it still set the page to A:H.

[Demeter]

Hello,

Can you post a sample file?
Or only the code you are using?
Think there is some small thing wrong in your code

[up4asmile]

Here is the first macro that I use. I does everything except define the print area.

Sub Googlebills()
'
' Macro1 Macro
' Macro recorded 7/2/2007 by
'

'
Cells.Select
Range("D1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With


Columns("L:S").Delete
ActiveSheet.Cells(1, 12) = "Paid by Distributor"
ActiveSheet.Cells(1, 13) = "Paid by EXAIR"
Columns("L:M").Select
Selection.NumberFormat = "$0.00"
Selection.ColumnWidth = 18



Columns("F:F").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],pcodes,3,FALSE)"
Selection.AutoFill Destination:=Range("F2:F672"), Type:=xlFillDefault
Range("F2:F672").Select
ActiveWindow.SmallScroll Down:=-63
ActiveWindow.ScrollRow = 1
Dim ws As Worksheet
Dim icnt()
Dim ccnt()
Dim tot(2) As Single
Dim timp(2) As Long
Dim tclk(2) As Long
Dim costcnt()
rr = 2
Set ws = ActiveSheet
Do While ws.Cells(rr, 4) <> "" Or ws.Cells(rr + 1, 4) <> ""
ReDim icnt(2)
ReDim ccnt(2)
ReDim costcnt(2)
sr = rr
Do While ws.Cells(rr, 5) <> ""
If ws.Cells(rr, 6) = "A List" Then ii = 1 Else ii = 2
icnt(ii) = icnt(ii) + Val(ws.Cells(rr, 7))
ccnt(ii) = ccnt(ii) + Val(ws.Cells(rr, 8))
tot(ii) = tot(ii) + Val(ws.Cells(rr, 10))
timp(ii) = timp(ii) + Val(ws.Cells(rr, 7))
tclk(ii) = tclk(ii) + Val(ws.Cells(rr, 8))
costcnt(ii) = costcnt(ii) + Val(ws.Cells(rr, 10))
rr = rr + 1
Loop
ws.Cells(rr, 5).FormulaR1C1 = "=VLOOKUP(" & ws.Cells(rr - 1, 3) & ",pcodenames,3,FALSE)"

ws.Cells(rr, 1).EntireRow.Insert
ws.Cells(rr, 1).EntireRow.Insert
ws.Cells(rr, 5) = "A list"
ws.Cells(rr + 1, 5) = "B list"
ws.Cells(rr, 7) = icnt(1)
ws.Cells(rr + 1, 7) = icnt(2)
ws.Cells(rr, 8) = ccnt(1)
ws.Cells(rr + 1, 8) = ccnt(2)
ws.Cells(rr, 10) = costcnt(1)
ws.Cells(rr, 12) = (costcnt(1) / 2)
ws.Cells(rr, 13) = (costcnt(1) / 2)


ws.Cells(rr + 1, 10) = costcnt(2)
ws.Cells(rr + 1, 12) = (costcnt(2) / 2)
ws.Cells(rr + 1, 13) = (costcnt(2) / 2)
For rr = rr - 1 To sr Step -1
ws.Cells(rr, 1).EntireRow.Delete
Next rr

rr = rr + 4

'ws.Cells(rr, 3).FormulaR1C1 = "=VLOOKUP(ws.cells(rr-3,2),pcodenames,3,FALSE)"

Loop
rr = rr + 3
ws.Cells(rr, 5) = "A List Totals"
ws.Cells(rr, 5).Font.Bold = True
ws.Cells(rr, 12) = tot(1) / 2
ws.Cells(rr, 13) = tot(1) / 2
ws.Cells(rr, 7) = timp(1)
ws.Cells(rr, 8) = tclk(1)

ws.Cells(rr + 1, 5) = "B List Totals"
ws.Cells(rr + 1, 5).Font.Bold = True
ws.Cells(rr + 1, 12) = tot(2) / 2
ws.Cells(rr + 1, 13) = tot(2) / 2
ws.Cells(rr + 1, 7) = timp(2)
ws.Cells(rr + 1, 8) = tclk(2)

ws.Cells(rr + 2, 7) = timp(1) + timp(2)
ws.Cells(rr + 2, 8) = tclk(1) + tclk(2)
ws.Cells(rr + 2, 12) = tot(1) / 2 + tot(2) / 2
ws.Cells(rr + 2, 13) = tot(1) / 2 + tot(2) / 2

ws.Cells(rr + 2, 1).EntireRow.Font.Bold = True


Columns("A:D").Delete
Columns("B:B").Delete
ActiveSheet.Cells(1, 1) = "Product Category"



With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""-,Bold""&12Google " & ActiveSheet.Name & " 2014 Bill"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With


Columns("G:G").Select
Selection.ColumnWidth = 18




End Sub
Then I made a new macro containing only the code that you gave me. If it gets to the point where the code works, then I will add it to the end of the current macro. (At least that's the plan.)
This macro was created quite a while ago and may have some extra stuff in it that it doesn't need. It basically takes a report on costs, and divides the cost between us and our distributor, and shows how much cost was in each product line.

I hope this helps.

[Demeter]

Dont see anything strange, except for some minor improvements

Should work if you change your last part of code to:

Please Login or Register  to view this content.

[up4asmile]

I'll give it a try. Thank you!

Nope, I tried it, with the same results.

I'm going to shelve this till time to do next month's reports. Time to move on and update more macros.

Thank you so much!!!!