Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding Boarders to a Print Area (2003)

    Hi,
    I have spreadsheet, with a macro and I want to add to the macro that once I select my data list, it puts boarders around all the cells. Here is my current code:
    Sub RichardPrintArea()
    ' RichardPrintArea Macro
    ' Macro recorded 6/20/2005 by Deb Buck
    ActiveWorkbook.Names.Add Name:="Print_Area", RefersToR1C1:= _
    "=OFFSET(Sheet1!R1C1,0,0,COUNTA(Sheet1!C1)+5,1 3)"
    With ActiveSheet.pagesetup
    .PrintTitleRows = "$1:$10"
    .PrintTitleColumns = ""
    End With
    ActiveSheet.pagesetup.PrintArea = "$A$1:$M$39"
    With ActiveSheet.pagesetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = "&8Page &P of &N"
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(0.75)
    .BottomMargin = Application.InchesToPoints(0.75)
    .HeaderMargin = Application.InchesToPoints(0.04)
    .FooterMargin = Application.InchesToPoints(0.04)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLegal
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = 100
    .PrintErrors = xlPrintErrorsDisplayed
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End Sub
    Thanks,
    Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Boarders to a Print Area (2003)

    I'd put a classified ad in the local newspaper: "Boarders wanted". <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    But seriously, do you want borders around the outside of the print area? Or do you want borders around each individual cell?

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Boarders to a Print Area (2003)

    Thanks, what line would I add that code after?
    Thanks,
    Deb

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Boarders to a Print Area (2003)

    If I understand what you are asking, you need to add something like this:

    With ActiveSheet.Range("PrintArea").Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("PrintArea").Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("PrintArea").Borders(xlEdgeBotto m)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("PrintArea").Borders(xlEdgeRight )
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With ctiveSheet.Range("PrintArea").Borders(xlInsideVert ical)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With ctiveSheet.Range("PrintArea").Borders(xlInsideHori zontal)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Boarders to a Print Area (2003)

    Put it BEFORE this line:

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Boarders to a Print Area (2003)

    It gave me an application define error message. and highlighted the first line of code you gave me.
    Deb

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Boarders to a Print Area (2003)

    I used the code before in access exporting to excel, but this doesn't work in Excel. It gives me an Object Required error message.

    Dim c As Object
    For Each c In r.Cells
    c.BorderAround ColorIndex:=xlAutomatic, Weight:=xlThin, LineStyle:=xlContinuous
    Next

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Boarders to a Print Area (2003)

    Sorry, I left the underline out of the defined name for the print area. The code should read:

    With ActiveSheet.Range("Print_Area").Borders(xlEdgeLeft )
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("Print_Area").Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("Print_Area").Borders(xlEdgeBott om)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("Print_Area").Borders(xlEdgeRigh t)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("Print_Area").Borders(xlInsideVe rtical)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("Print_Area").Borders(xlInsideHo rizontal)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    Legare Coleman

  9. #9
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Boarders to a Print Area (2003)

    Ok, that worked, sorta. I have 2 problems. The first is I only wanted the borders around my data list. Which will always start at A11 and go to column M, but the amount of rows will change.

    My second problem is I don't want to hard code this line ActiveSheet.pagesetup.PrintArea = "a1:m91" because once again, the number of rows will change.

    Thanks,
    Deb

  10. #10
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Boarders to a Print Area (2003)

    It's a little closer, but the code is still putting borders around rows 1-9 and it added borders on about 20 blank rows after the last row of data. I noticed in the borders text we're still calling "print_area" but I don't see that defined anywhere, should that be changed to something else?
    Thanks,
    Deb

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Boarders to a Print Area (2003)

    OK, does this do what you want?

    Sub RichardPrintArea()
    ' RichardPrintArea Macro
    ' Macro recorded 6/20/2005 by Deb Buck
    Dim lLastRow As Long
    With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$10"
    .PrintTitleColumns = ""
    End With
    lLastRow = ActiveSheet.Range("M65536").End(xlUp).Row
    ActiveSheet.PageSetup.PrintArea = "$A$11:$M$" & lLastRow
    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = "&8Page &P of &N"
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(0.75)
    .BottomMargin = Application.InchesToPoints(0.75)
    .HeaderMargin = Application.InchesToPoints(0.04)
    .FooterMargin = Application.InchesToPoints(0.04)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLegal
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = 100
    .PrintErrors = xlPrintErrorsDisplayed
    End With
    With ActiveSheet.Range("Print_Area").Borders(xlEdgeLeft )
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("Print_Area").Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("Print_Area").Borders(xlEdgeBott om)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("Print_Area").Borders(xlEdgeRigh t)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("Print_Area").Borders(xlInsideVe rtical)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("Print_Area").Borders(xlInsideHo rizontal)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End Sub
    Legare Coleman

  12. #12
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Boarders to a Print Area (2003)

    I did some more testing and I misspoke in my previous post where I said it only added about 20 extra rows. It's adding much more than that, it's adding about 4 extra pages of blank rows. I changed the code slightly to this (see below), where I changed the "Print_Area" to "ReconcileArea" which is a defined range to only select rows 11 to the number of rows of data. That seemed to work and the borders only went around those rows and rows 1-10. It didn't put borders around all those other blank rows. I still don't want the borders around rows 1-10.

    But it seems like Excel is still thinking that there is data below the actual data list because it prints out 4 extra blank pages. I've selected those rows to make sure there is nothing down there but I can't seem to fix that. I'm sure there's something in the code that I'm not understanding that says the printarea includes all those blanks rows.
    Thanks,
    Deb

    Sub RichardPrintArea()
    ' RichardPrintArea Macro
    ' Macro recorded 6/20/2005 by Deb Buck
    Dim lLastRow As Long
    With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$10"
    .PrintTitleColumns = ""
    End With
    lLastRow = ActiveSheet.Range("M65536").End(xlUp).Row
    ActiveSheet.PageSetup.PrintArea = "$A$1:$M$1" & lLastRow
    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = "&8Page &P of &N"
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(0.75)
    .BottomMargin = Application.InchesToPoints(0.75)
    .HeaderMargin = Application.InchesToPoints(0.04)
    .FooterMargin = Application.InchesToPoints(0.04)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 100
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLegal
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = 100
    .PrintErrors = xlPrintErrorsDisplayed
    End With
    With ActiveSheet.Range("ReconcileArea").Borders(xlEdgeL eft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("ReconcileArea").Borders(xlEdgeT op)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("ReconcileArea").Borders(xlEdgeB ottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("ReconcileArea").Borders(xlEdgeR ight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("ReconcileArea").Borders(xlInsid eVertical)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With ActiveSheet.Range("ReconcileArea").Borders(xlInsid eHorizontal)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End Sub

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Boarders to a Print Area (2003)

    Sorry, there were two typos in one line of code in my last post. I have edited that post to correct the errors. One of your previous posts indicated you wanted to start printing at row 11, but this post indicated you want to start at row 10. I wasn't sure which you wanted, so I changed the code to start at row 11. If you want to start at row, then change this statement:

    ActiveSheet.PageSetup.PrintArea = "$A$11:$M$" & lLastRow

    to

    ActiveSheet.PageSetup.PrintArea = "$A$10:$M$" & lLastRow
    Legare Coleman

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Boarders to a Print Area (2003)

    Good try, but not quite right. See my other post and corrected code.
    Legare Coleman

  15. #15
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Boarders to a Print Area (2003)

    Ok, I replaced your code but now it only prints to row 11. Maybe I didn't make what I wanted clear before. Rows 1-10 is a header and I need that to print, but no borders. Starting at row 11 is where the data list starts and that number of rows will vary from month to month. So the borders didn't print on rows 1-10 so that's fixed.
    Thanks,
    Deb

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •