Free Microsoft Excel 2013 Quick Reference

Create automatic bottom border at page break in table

I work with tables that are varying in length between 10 and 100 pages.

I am trying to figure how to automatically create a bottom border to the table at each page break. It is time consuming to do it manually ech time a row is removed or added within the table.

To further complicate this, I use the top 5 rows as Print Titles. These rows are of varying heights.


Post your answer or comment

comments powered by Disqus

I have a problem concerning deleting automatic page breaks in excel
2003. I found this code in a previous discussion:

Sub DeleteHPageBreaks()
Dim pb As HPageBreak
Dim lCount As Long
For lCount = Sheet1.HPageBreaks.Count To 1 Step -1
Set pb = Sheet1.HPageBreaks(lCount)
If pb.Type = xlPageBreakAutomatic Then pb.Delete
Next lCount
End Sub

The code seems to work fine in previous excel versions, but not in
2003. Does anyone know how to solve this in excel 2003?

I would appreciate your help!


How can I disable automatic page breaks in Excell 2003

Please, can anyone help me with the shortcomings mentioned here about a "page-break" instruction in a Words document in a code when running in Excel.

In addition, the normal code in Words for MoveUp" does not work, I want to get up into an imported table with data and adjust the characteristics of the table in order to fit the page in Words.

Thanks in advance for kind assistance
/ bjorn

This is my very first attempt to seek advice in programming VBA.
Background: XP professional, Office 2000

The context & the problem :

Running a long program in Excel with many macros / coded subs in background.
The program will result in a lot of reports, typically of 5-25 pages each & with a lot of conditional text created in excel macros / VBA
I want (must) get print out in Words as such printout is smoother.
Everything is - be the end of a lot of work - OK, except to make a page-break in the excel routine that runs what to be done in the created word document.
Some instructions are OK, for example to rearrange margins, others, for example pagehead and pagefoot does not work, but WORST:
In words, the "instruction" for page break is - .InsertBreak Type:=wdPageBreak - to be put within With wrd (= stands for Words..) and With .selection.
In words, it works, but when running from Excel, it does NOT work.

The code under Excel used for this small program sequence is found below
' Open a document
Set wrd = GetObject(, "Word.Application"):

With wrd:
With .Selection
.TypeText Text:="This is page 1"

'.Collapse Direction:=wdCollapseEnd
'.InsertBreak Type:=wdPageBreak ' tried - did not work

'.InsertBreak Type:=wdSectionBreakContinuous, ' tried - did not work
.TypeText Text:="Some text on page 2"
.Sections.Add ' this works, but text instructions after this command IS NOT placed after a page break
' .MoveEndUntil Cset:="a ", Count:=wdForward, tried.... did not work
' .MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
.TypeText Text:="Here should be text, for example to state that we are now on page 2..."
End With
End With

Is it possible to have some help on this (trivial ??) matter ?
Regards Bjorn

Most Searched Demo: Take control of page breaks in your Word document

Watch these demos to find out how to manage page-break options and achieve the best pagination for your document.

Hi everybody
Is there a way to get the row at page break point?

I cannot do manual, or drag and drop, page breaks in Excel. A large white
cross appears when I cursor over a cell. I cannot find a way to enlarge a
page break/print area, nor to make it narrower, except by using "set print
area" which is not the same as dragging the print area boundary.
I used to be able to do this!! Can anyone help? Thanks.

I used to be able to drag and drop page breaks in Excel. I can no longer do
that on my system. Does anyone know why? I can "reset print area" but this
isn't the same and does not have the same outcome.

add consecutive numbers but start over at a break in them. I'm trying to
calculate consecutive days of attendance.

I have Office 2000 Professional installed. I am unable to move page breaks in
Page Break Preview. I have no idea why this is a problem now. I had always
been able to adjust the page breaks using Page Break Preview. Why am I having
this problem now? I would appreciate your thoughts on this subject.

P.S. I'm also having a problem with Word in the same suite. It unexpectedly
quits when I open a spanish language document. Anyone have any clues why?

I suddenly can't adjust my page breaks in any Excel spreadsheet while in Page
Break Preview. Is there some option to turn it on and off?

I have a worksheet that is setup fit to page. Right now it has about 1000 rows of data with about 200 sections that can have anywhere from 3 to 6 rows allotted per section.

The problem is when I print, invariably some sections end up page breaking in the middle of a section.

Is there any way in excel to insure that certain rows of data will never page break. I cannot insert manual breaks as the size of the sections and the size of the data will continually change.

I know in word there is something called keep lines together that prevents paragraphs from page breaking. I need something similar in excel.

Any help is greatly appreciated.

Hello all! First i want to say this forum has been a great tool with my new job and helping me compile codes that makes our department reporting run much faster.

Now what i am trying to do is count cells with data in between page breaks in a given column, and have that number show in a cell that will always be above the page break in a given row.


clm clm clm clm

data data data data

data data data data
date data data data

data data data data

data data data data
i have a macro that is currently sorting the data and adding rows and page breaks, now if it could only count cells with data in x column and input that number into column x right above the pag break.

Any help is appreciated!

am hoping someone can help with this? I have been searching for a solution for a long time now.

What I need to come up with is a macro that will create a new worksheet at each change in Data.

I have a worksheet full of data, and at each change in the data of column A I would like the a new worksheet created within the same workbook. Something to the effect of:

Column A

The macro would take the above data, and create three additional worksheets. 1 with all the rows related to bill, one with the rows related to Jack, and one for Dave. What I am finding difficult to account for is the fact the data in column A can change. For example, in the above list "bill" appears 3 times. But in actuality "bill" could have any number of rows of data.

anyone have any ideas?


How do I remove a manual page break in Excel with Office 2003?

I have a macro but its not working. I need a Macro to put bottom border at last row with data. The last column is always difrrent as well so i need it to find the last row and column with data and place a bottom border. I was using a code with the lin below but i whink i have soomething wrong.

    With Cells
        LastRow = .Find("*", .Cells(1, 1), xlValues, xlPart, xlByRows, xlPrevious, False, False).Row
        LastCol = .Find("*", .Cells(1, 1), xlValues, xlPart, xlByColumns, xlPrevious, False, False).Column
            .Range(.Cells(LastRow, 1), .Cells(LastRow, LastCol)).Borders (xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With

I am trying to het a macro I am writing to apply page breaks where I want them.

I used this command:

Set ActiveSheet.HPageBreaks(1).Location = Range("A87")
Set ActiveSheet.HPageBreaks(2).Location = Range("A175")
Set ActiveSheet.HPageBreaks(3).Location = Range("A266")

I get a message:

"run time error (9)
Subscripts out of range"

When I hit the dubug option, it highlights the row:
Set ActiveSheet.HPageBreaks(3).Location = Range("A266")

Will Excel only allow for 3 page breaks in macros?


I need to create a macro that create a worksheet for each page break from the original worksheet and copy the data from the original worksheet.
It's more complcate for me.....
thank you

So I have a file and have inserted page breaks in at certain intervals. At every change in the data in a particular column can Excel extract the corresponding rows, and create a new file using the particular column data as a file name? Does that make sense?

Thank you,

Chris C.

am preparing a documents in excel which consist several pages with various
columns. how can i add continious bottom border (on end of each page) on each
page automatically.. in normal case, if it is continious columns, it's not
providing bottom border in automatic page brake and we hv to draw it manually
and then after if we add any new row, this manually drawn bottom border will
move to next page.. is there any solution to add automatic border on each
page brake ??

Hello Ozgrid!

I've a macro that pulls data from another workbook and inserts rows throughout the current worksheet. As a result, the borders drawn on that table for aesthetics at page breaks no longer rest at the bottom of the page once the macro has run. The user has to then manually remove the old double-border and then format it in the appropriate location. This gets to be a hassle when the worksheet is 10+ pages and the rest of the process is already automated.

So, my question is whether it's possible to detect page breaks using VBA, then have it format the row above that page break. I attached a partial sample of the workbook after the macro has been run. Thanks!

I have an intermittent problem with excel not resetting automatic page breaks below a hard page break. I think it has something to do with some macros that I run but I have no idea which ones they could be. I thought I had it narrowed down a while back so I stopped using some but the problem is coming back. I’ve tried searching here in the past but was unable to find a solution.

To more clearly explain the problem.

Say the first automatic page break is at row 100. I insert a hard horizontal page break at Row 95. The automatic page break doesn’t go away. There’s a hard break at row 95 and a soft break at row 100. It doesn’t affect printing but it’s a problem for me because I have a macro that finds the automatic page breaks and it still picks up this bogus page break.

I am creating a quote template in excel.

To get the correct 'look and feel', I have used borders. This is fine if the
quote can be contained on a single printed sheet, however, as soon as I go
into a second page, I cannot get the border to recognise a page break. In
other words, I have a series of vertical border lines but no horizontal
border to signafy the end of one page and the start of another - Can this be

Many thanks!

Excel 2002

I have a little problem and I would like to know if there is a solution
to it...

I have a very long worksheet, with two buttons allowing to switch
between the short and the full view. Obviously, I would like to correct
the page breaks in some instances, e.g. where a section title is at the
end of a page. However, if I change an automatic page break to a manual
page break, the summary view printing gets all screwed up, as it is
affected of the same manual page breaks. Any idea what could help me?

(I CANNOT set two worksheet for both view, as the update of the data
would be an issue.) I guess I might just leave the automatic breaks as
is, but it sux!:P


Turquoise_dax's Profile:
View this thread:

I was wondering if there is a way to link columns, so once the page break is
reached that Excel will automatically continue a list in a designated column
to the right?

I am trying to create a multi-paged checklist of DVD titles. The sheet is
mostly text based, created in Excel 97. The eventual goal is to use this
checklist as a template to catalog all other types of media too (CDs, video
game carts,...).

The spreadsheet is 4 Columns across (A to D) and each page in the
spreadsheet is 30 rows in length. Columns 'A' and 'C' will have check-boxes;
Columns 'B' and 'D' are the DVD Titles (text). I have set the page break to
be every 30 rows (row 31, 61, 91, 121, etc.... )

The problem I am having is trying to find a way to get Excel to
automatically continue text data to the next text column on the same
worksheet page. When both text columns are filled, I would like Excel to
automatically move to the next text column, on following page.

To be a bit more clear--- When I have filled all the 30 rows in column 'B',
for Excel to automatically continue the list in column 'D'. When all 30 rows
of column 'D' (page 1) are filled, I would like Excel to automatically
continue the list on the next page, starting back in column 'B'.

By default, Excel just keeps the list going in one column, making new pages
every 30 rows. That makes for a very long list, especially when it comes to

I am sure there is probably a very easy solution, but for the life of me,
I'm stumped.

I would grateful for any kind help from forum members. I am still an extreme
noob when it comes to any formulas or VBA coding.

Cheers and thanks in advance,

Shayne T.

No luck finding an answer? You could always try Google.