[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Excel VBA Selecting Range of Cells to bottom of Print Area Page

Posted on 2012-08-14
Medium Priority
Last Modified: 2012-08-14
I am trying to create an invoice in Excel. I have a precreated template on a sheet. The macro cleans up the information from a report that is exported from another software. It always comes out formatted the same(column-wise). As it stands now, It creates the bottom section of the invoice, then selections the used range for the cells on the bottom half then puts borders around them. What I am unsure on how to do is select only the first page border each section, then move to the second page and paste the header of the invoice again on the second page, then border the sections of the data again. To start the macro, you run the Create Invoice macro. If you have any questions, please let me know.

Any help is much appreciated. I'm attaching the sheet with the VB code in it. Feel free to critique this also. :)
Question by:tekkieguru
  • 2
LVL 13

Accepted Solution

Ryan earned 2000 total points
ID: 38293074
Well, I can answer half of your question.  To repeat you header, say rows 1:6, in page setup set the "repeat rows at top" to 1:6.  You may want to move the date down 1 row, or shrink the image and use rows 1:5.

As for putting a border around the data thats on 1 page, even if it breaks onto a 2nd page, I'm not sure.  (Rewording if someone else has trouble understanding what you were asking for).
LVL 13

Expert Comment

ID: 38293128
Found the answer to the other half.  Conditional format.  Currently, I'm seeing 46 rows on a page, with 6rows as header (so 40 rows of actual data_. So you want a bottom border on every data thats a multiple of 40 + 6, =MOD(ROW()-6,40)=0

Same thing for the top border on the next line, =MOD(ROW()-5,40)=0

Author Closing Comment

ID: 38294390
That worked great for the repeating header. I actually looked  at that section, but wasnt quite sure how to set it up.

As far as the page break borders. I used the below code. Since it was just needed on the bottom of the invoice data section, i figured out how to find the page breaks.

Private Sub PageBreakBorders()
Dim pb As HPageBreak
For Each pb In ActiveSheet.HPageBreaks
    LastRow = pb.Location.Offset(-1, 0).Row
    Range("B" & LastRow, "H" & LastRow).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

End Sub

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question