Hpagebreaks vba excel

VBA: HPageBreaks.Location

Domenic

MrExcel MVP

Does anyone know why the following code does not move the first horizontal page break to the specified location?

Set CurrCell = ActiveCell

It seems to work if I replace.

So why doesn’t it work with the Location property?

Some videos you may like

Excel Facts

MrExcel MVP

If there are no HPBs (i.e., the used range all fits on a single sheet), then that line generates a run-time (subscript) error — that makes sense.

If rows 1:4 are so tall (or the vertical margins so large) that there’s an automatic page break before row 5, that line can’t change the position — that makes sense.

But if neither of those is true, it still doesn’t move — that escapes me.

I may be losing it, but fill A1:E20 with =CELL(«address», A1) and run this:

Domenic

MrExcel MVP

Thanks for your help, shg, I really appreciate it!

Makes sense. However, my used range extends beyond the first page.

Yes, this makes sense too. However, again, this is not the case.

I may be losing it, but fill A1:E20 with =CELL(«address», A1) and run this:

I see that it places the contents of E10 at the first horizontal page break in A5. Obviously this is not the desired result. So I’m not sure what to make of it. Bug? If you try the following code, you’ll see that the second horizontal page break does not move, as expected.

Set CurrCell = ActiveCell

Norie

Well-known Member

This doesn’t really make sense at all, Location returns a range so when you use something like this:

It’s putting the value from E5 into the location of the page break, not changing the actual location.

However it does say in Help, and the Object Browser indicates it too, that it should change the location.

I’ve tried using Set but that errored, and obviously when you try the address as a string if puts the string in the location.

Norie

Well-known Member

Wait a minute, Set does work — I must have had a typo or something in the original code.

Wait another minute it only seems to work when you are in Page Break Preview mode.

So how do you do that without manually changing to that mode?

Norie

Well-known Member

This is getting weirder.

I found out how to change to page break mode in code and tried this.

Domenic

MrExcel MVP

Thanks Norie, very much appreciated!

This is getting weirder.

I found out how to change to page break mode in code and tried this.

Actually, this seems to work. It moves the last horizontal page break to Row 45. And, in doing so, it re-calculates subsequent page breaks to their default settings. It looks like this is probably the intended result in this circumstance. However, if a page break is moved within page breaks on either side of it, it works as expected.

So there are three things that I’ve learned.

Vba Vpagebreaks And Hpagebreaks

Vba Vpagebreaks And Hpagebreaks — Excel

I am running into a unique situation.
I only want to reset the HPageBreaks because those always need to be reset when a macro is called. I don’t need to reset the vertical pagebreaks. Is there a ways to only reset the horizontal pagebreaks and leave the vertical pagebreaks in place.

I am using the Sheets(«Sheet1»)ResetAllPageBreaks

I also have tried after the Sheets(«Sheet1»)ResetAllPageBreaks is called to check do a
Code:

This works only once. If you run the macro again it bombs.

Subscribe for Weekly Excel Tips and Tricks

Helpful tutorials delivered to your email!

Similar Topics

How To Create Vertical Bar Chart — Excel

Seemingly super simple, but I can’t figure it out.

When I create a bar chart, the bars are horizontal. I want the chart bars to be vertical. It tried to rotate the chart so that it is vertical, but the «rotate» options are greyed out.

How do I get those bars vertical?

Macro To Save Then Clear Form Data — Excel

I have an excel form, I want my users to be able to open the form, fill in appropriate data, then click the save button. When the button is clicked, the data in the form should be saved to a new file and then the original form should be reset (clear all values) for a new set of data entry. When the data is saved, I want the new excel sheet to be saved to a location specified within the macro so the user doesn’t have any options and I want the title of the new file to be the value of one of the cells.

I’m new to macros and would appreciate any help this board can offer. Thanks.

Ctrl+end Goes Beyond Data — Excel

How can I reset where Excel thinks the end of may data is?

When I press Ctrl+End it highlishts column ANC when really my data now finished at Column AC

Unfilter/showalldata — Excel

I have just tried running the following code

in the hope that it might ‘unfilter’ my table but it literally doesn’t do a thing!

My table is called Activity_Table

Can you provide me with something that will ‘unfilter’ my table if it is filtered on any or all 6 columns?

Can’t Copy And Paste Or Paste Special Between Excel Workbooks — Excel

We have a number of Excel users in our office who cannot copy and paste
between Excel workbooks. They can copy and paste between worksheets. When
you highlight the section to copy and then go to the new workbook both the
paste
and paste special are «grayed out». This is true whether you right-click the
mouse, go to the edit menu, or use control keys. This occurs with any data
type and the most simple workbooks. I have seen some suggestions here but
none have worked for this particular problem. I have reset the menus and
renamed the .xlb files and neither helps. You can open the clipboard and the
paste will work, but there is no paste special option. Any help would be
greatly appreciated. Thanks!

Counting Number Of Times «name» Appears Across Multiple Sheets — Excel

Hello, I’m trying to count the number of times a name appears across multiple worksheets but I can’t seem to figure it out.

I have 6 sheets to count and all the names are in column A (from A1:A100) on each sheet. The names are not in a particular order.

On sheet 7 I want to have a cell beside each persons name that counts the number of times their name appeared on the other 6 sheets.

For example Sheet 7 (called total) would have

Joe 5 (where 5 is the outcome of the formula I’m looking for to count all the times «Joe» appears on sheets 1-6).

I have it counting per sheet with =COUNTIF(A1:A130,»name») but this is not quite what I am looking for.

Thanks in advance for any advice.

Edit: I am using Excel 2007 w/ windows XP

Fill A Combobox With A Named Range — Excel

This is a very basic question, but i have been struggling for hours, so I must ask.

I I trying to simply fill a combobox on a userform from a named range on a sheet, I want to populate the combobox as soon as the userform starts. (I launch the userform from a button on the sheet) Where must I place the code? and will this code work?
Code:

I am very new to vba, so please excuse my ignorance,
any help would be greatly appreciated

Execute Code After A Data Connection Refresh Is Finished — Excel

I have a piece of code called ConvertDates that formats data contained on 6 worksheets. The 6 data sheets all contain a data connection to a website of foreign exchange tables.

What I want is for my code to execute as soon as the data connection refresh has finished.

When I use the statement

The code executes the macro whilst the refresh is still happening, thereby screwing up my results. I don’t really want to use a timed wait, because the refresh speed is going to vary from user to user.

Is there some way I can tell excel to wait till the refresh has finished and then execute the code?

Any help would be hugely appreciated.

Macro: If A1 Is Blank, Run Macro X, Else Do Nothing — Excel

I am trying to get my macro to do this on open. Check if a1 is empty, if its empty run another macro (which is an input box to put something IN a1), but if A1 has something in it, DO NOTHING.

I keep getting errors, saying I’m not formulating my if statements correctly.

This is what I have:
Code:

Macro To Zoom To Fit Window, Then Apply That Zoom Factor To Other Sheets — Excel

I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy.

I have set it up so that everything looks OK and is visible on MY screen, but I’m conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them.

I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.
Here’s the code that does it.
Code:

By repeating this code for each worksheet, I can make each one be zoomed just right.

However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
What I want to do is go to the worksheet that has the largest number of rows (it’s always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is — and it will vary depending on the user — and apply that to the other worksheets that have a similar layout.

I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I’d like them to have a consistent appearance.
I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that’s not ideal either, because some of the row heights vary from sheet to sheet, and again I’ll end up with different font sizes.

Reset HPageBreaks via vba

Can anyone please let me know how to reset the HPageBreaks in my form via vba? I search the forum, and found how to activate them, but not remove them.

1. ActiveSheet.HPageBreaks.Reset

1. ActiveSheet.HPageBreaks.Delete

No luck with either.

Re: Reset HPageBreaks via vba

This will remove all hpagebreaks
[vba]do while activesheet.hpagebreaks.count>0
ActiveSheet.HPageBreaks(activesheet.hpagebreaks.count).Delete
loop[/vba]

[h4] Cheers
Andy [/h4]

Re: Reset HPageBreaks via vba

Thanks for responding Andy,
The code faults with a 1004 error. Here is the actual code I am using. Perhaps something is preventing the other?

1. Private Sub CommandButton3_Click()
2. Range(«D9:I500»).Select
3. Selection.ClearContents
4. Selection.FormatConditions.Delete
5. Range(«D9»).Select
6. Do While ActiveSheet.HPageBreaks.Count > 0
7. ActiveSheet.HPageBreaks(ActiveSheet.HPageBreaks.Count).Delete
8. Loop
9. End Sub

Re: Reset HPageBreaks via vba

I don’t get that error on my test workbook.
Can you post an example workbook?

[h4] Cheers
Andy [/h4]

Re: Reset HPageBreaks via vba

Andy,
The workbook is 4.5mb. The smallest I am able to compress the file is by 200k by 7zip. I can email it to you if that would be ok.

Re: Reset HPageBreaks via vba

That zipped file should be ok to email in my direction

[h4] Cheers
Andy [/h4]

Re: Reset HPageBreaks via vba

Good morning Andy,
Did you receive the email yesterday? Our server went down yesterday, and I’m not sure if it got there before the crash or not.

Re: Reset HPageBreaks via vba

Looking at it right now
Seems that even after clearing the range excel still thinks you have 2 pages to print.
You could just add error checking in there but I assume you want only 1 page when the form is empty?

[h4] Cheers
Andy [/h4]

Re: Reset HPageBreaks via vba

I was hoping that removing the Hpagebreaks would give me that solution.
Am I on the right track?

Re: Reset HPageBreaks via vba

Try this. You may want to adjust the seond set of cells incase the contents of column A varys.[vba]Private Sub CommandButton3_Click()
With Range(«D9:M500»)
.ClearContents
.FormatConditions.Delete
End With
With Range(«A60:C500»)
.ClearContents
.FormatConditions.Delete
End With

Range(«D9»).Select
Do While ActiveSheet.HPageBreaks.Count > 0
ActiveSheet.HPageBreaks(ActiveSheet.HPageBreaks.Count).Delete
Loop
End Sub[/vba]

[h4] Cheers
Andy [/h4]

Re: Reset HPageBreaks via vba

Andy,
I see what you are doing here. You think the empty cells in column A could be the root of the problem. Never thought of that. I assume since the data being populated was in columns D thru I, thats where the problem would be. Makes more sense to clear it all though.

I do still get the same error though. Doesn’t seem to like this line.

1. ActiveSheet.HPageBreaks(ActiveSheet.HPageBreaks.Count).Delete

Could it be because of Office 2003, or due to an addin I don’t have installed?

Re: Reset HPageBreaks via vba

I tested this in 2003 with no problems. No addin required.
I populated the sheet using «Summarize Work order» and then «Clear»

[h4] Cheers
Andy [/h4]

Re: Reset HPageBreaks via vba

Andy,
I don’t get it. I tried this on another computer here, and it doesn’t work there either. All of our computers have just been newly installed with Office 2003. Is there a setting under option that could be causing this?

We have only had Office 97 up until now, and I’m afraid noone here has any experiance with this new version yet.

I can only assume, that if its working on yours, then you have a setting somewhere that is different than our setting. This is kind of scary, because I would have to ensure that setting is in place across our entire company, and all other companies corperate wide.

I hope this is not the case. Any other Ideas?

Re: Reset HPageBreaks via vba

Here’s somthing interesting. If I populate by clicking the «Summarize Work order», and then click the «Clear», I mmediately get the Error’1004′. I click debug bringing me to my VB, and the

1. ActiveSheet.HPageBreaks(ActiveSheet.HPageBreaks.Count).Delete

is highlighted. I click the stop button, followed by the save button. The click the «Clear» again, and this time it works.

But when I re-populate with the «Summarize Work order», and clear again, Im back to the error again.

Re: Reset HPageBreaks via vba

Andy,
Thanks so much for your help with this. I have just received your email, and your workbook works where mine does not. I can see one difference between the 2. All of your cells on your worksheet contain «no fill», where all of mine had a white fill. When I run the macros on your workbook, they work fine.

So I did a test . I run the macros on your workbook, and everything is fine. I Fill the cells on your worksheet as white, and low and behold, I get the same error that I was getting on my workbook. I remove the «Fill» off of my workbook, and everything works fine.

Obviously, this is the cause for the error. I don’t quite understand why it would make a difference, but for some reason it does.

Thanks very much for working through this with me. I really appreciate your help.

Re: Reset HPageBreaks via vba

Thanks for posting back with a update on the reason.

The cells with interior colour will cause the number of pages to be such that those cells are included. The way you are clearing the cells currently does not remove the interior colour.
Not 100% sure but I don’t think you can delete the pagebreaks that are required in order to print the correct number of pages. Only when nothing is on the actual page can the breaks be removed.

[h4] Cheers
Andy [/h4]

Re: Reset HPageBreaks via vba

Andy,
Exactly right. I did sometesting with this, and the problem was solely based on the fill of the cells. I’m glad I sent this file to you, because I would have never been able to resolve that without getting the updated workbook back from you. Filling those cells isn’t really required anyway(strictly cosmetic), I have removed all fill colors.

Thanks again for your help with this. I couldn’t have done it without you.

Re: Reset HPageBreaks via vba

Although I know it is an old post I would like to add to this discussion for future reference.

Hpagebreaks vba excel

Excel ЧаВо

Worksbooks и Worksheets

Добавить новую пустую книгу:

В первом параметре метода Add можно указать стандартный тип шаблона Excel. Если же в нем указать имя (с полным путем) подготовленного файла (шаблоном может быть и «обычный» файл XLS, а не только файл XLT), то можно открыть книгу на диске как шаблон.

Откроет файл «MyTemplate1.xls», т.е. точно как обычный шаблон «Книга1.xls», но свой со своим форматированием, что позволит ускорить процесс экспорта данных в Excel, т.к. не придется форматировать ячейки и вызывать другие настройки листа.

Как задать количество листов в новой книге?

Задать количество листов в новой книге можно перед добавлением новой книги:

Как открыть книгу, имеющуюся на диске?

Если книга находится не в папке, указанной в Excel.Application.DefaultFilePath, то нужно указывать полный путь к открываемому файлу .xls, даже если файл находится в текущей папке вашего приложения, т.к. Excel ничего про него не знает.

Как закрыть книгу без вопросов о сохранении? Как закрыть все книги?

Как узнать имена всех открытых книг?

Узнать имена всех книг экземпляра Excel.Application можно в цикле, например, так:

Как найти определенную открытую книгу?

Точно так же, как в предыдущем ответе — по имени в свойстве Name. Если вы хотите сделать найденную книгу активной, то вызовите метод Activate

Переименовать книгу никак нельзя — только сохранить под другим именем методом SaveAs (смотрите «Как сохранить книгу»).

Как открыть текстовый файл в Excel’е?

Практически, так же как и обычную книгу, только внимательно указав дополнительные параметры в методе OpenText.

Как узнать имена всех листов в книге и их количество?

Узнать количество листов в книге можно в цикле по коллекции Workbook.Sheets. Количество листов — свойство Sheets.Count. Имя листа — свойство Worksheet.Name.

Как получить ссылку на активный лист в активной книге?

Обращаеясь к Excel.Application.ActiveSheet или WorkBook.ActiveSheet, вы получите ссылку на интерфейс IDispatch. Это происходит из-за того, что коллекция Excel.Application.Sheets может содержать объекты WorkSheet, Chart, Excel4MacroSheet (для поддержки Excel 4).

Определить тип листа можно, проверив свойство Worksheet.Type:

Как добавить новый лист в книгу? Как удалить лист?

При добавлении можно указать тип нового листа (WorkSheet, Chart, Excel4MacroSheet) и текущее положение. Добавленный лист будет активизирован автоматически (на него будет указывать свойство ActiveSheet)

Нужно ли делать лист активным, чтобы записать в него данные?

Не нужно — переключение (активация) листов только замедлит экспорт данных. Получите ссылку на любой лист в книге (активной или нет) и работайте c ней, как с активной. Активизировать лист нужно только в случае необходимости, например, при вставке из буфера обмена, предварительном просмотре и др.

Как выделить один или несколько листов в книге?

Как скопировать/переместить лист в одной книге? В другую книгу?

Как задать/убрать область печати? Как вызвать PrintPreview? Как напечатать лист?

Как установить параметры печати: отступы на листе, ориентацию листа и др.?

Установка параметров печати — довольно продолжительный процесс, поэтому советую настроить их в предварительно подготовленном шаблоне. Все параметры печати задаются в свойстве PageSetup объекта Worksheet. Но учтите, что текст в свойствах Footer или Header для Left, Right, Center суммарно не должен превышать 255 символов.

Для задания отступов в сантиметрах используйте функцию Excel.Application.CentimetersToPoints

Если вы выполните данный код, то заметите, как долго Excel настраивает все границы печати.

Как установить свои разрывы страниц (HPageBreaks) в области печати? Как узнать номер строки HpagePreak?

Для того, чтобы «появились» автоматические разрывы страниц, нужно перейти в режим разметки.

Также вы можете «переместить» разрыв, присвоив новое значение свойству Location объекта HPageBreak

Как сделать так, чтобы на каждой странице повторялись заголовки колонок таблицы?

Нужно задать «сквозные» строки заголовка таблицы.

Как установить пароль на существующий лист/книгу?

Как создать макрос из Delphi? Как выполнить макрос, имеющийся в книге?

Вам не удастся создать макрос программно, т.к. по умолчанию в Excel VBA Project отключен доступ к VBA из программ. Как включить эту возможность, читайте «PRB: Programmatic Access to Office XP VBA Project Is Denied»

Пример создания макроса с параметром и вызов его из программы:

Если все же вам очень нужно добавить макрос, независимо от настроек доступа к VBA Project, то можно воспользоваться листом макросов xlExcel4MacroSheet. Макроязык представляет собой «команды».

Будет ли работать макрос при выставленной «Очень высокая безопасность макросов» в Excel’е пользователя?

Записанные макросы в книге, открытой вашей программой всегда будут работать, независимо от выставленного в Excel’е «Уровня безопасности» для макросов.

Почему не работает макрос, записанный в книге?

Записанный в книге макрос может не работать по причине установленного антивируса. Например, установленный «Kaspersky Office Guard», входящий в состав «Антивирус Касперского», начисто отключает все вызовы VBA.

Hpagebreaks vba excel

How can I, using VBA, determine where a pagebreak takes place?
. if it is posible.

You will probably need to loop through the cells in a range (sheet) and
perform a check on each cell:

If Activecell.EntireRow.PageBreak <> -4142 then
MsgBox «Break above this cell»
End If

Hope this helps
Rowan

> How can I, using VBA, determine where a pagebreak takes place?
> . if it is posible.

Here is a method to get an array of horizontal pagebreaks and vertical
pagebreaks. The horizontal pagebreaks are a list of rows that have the
pagebreak and vertical a list of column numbers:

Sub Tester1()
Dim horzpbArray()
Dim verpbArray()
ThisWorkbook.Names.Add Name:=»hzPB», _
RefersToR1C1:=»=GET.DOCUMENT(64,»»Sheet1″»)»
ThisWorkbook.Names.Add Name:=»vPB», _
RefersToR1C1:=»=GET.DOCUMENT(65,»»Sheet1″»)»
i = 1
While Not IsError(Evaluate(«Index(hzPB,» & i & «)»))
ReDim Preserve horzpbArray(1 To i)
horzpbArray(i) = Evaluate(«Index(hzPB,» & i & «)»)
i = i + 1
Wend
ReDim Preserve horzpbArray(1 To i — 1)
Debug.Print «Horizontal Pagebreaks (rows):»
For J = LBound(horzpbArray, 1) To UBound(horzpbArray, 1)
Debug.Print J, horzpbArray(J)
Next J

i = 1
While Not IsError(Evaluate(«Index(vPB,» & i & «)»))
ReDim Preserve verpbArray(1 To i)
verpbArray(i) = Evaluate(«Index(vPB,» & i & «)»)
i = i + 1
Wend
ReDim Preserve verpbArray(1 To i — 1)
Debug.Print «Vertical Pagebreaks (columns):»
For J = LBound(verpbArray, 1) To UBound(verpbArray, 1)
Debug.Print J, verpbArray(J)
Next J
End Sub

This uses an Excel 4 macro to get this information. This is much faster
than the VBA pagebreak which uses the printer driver and can be very slow.

The is a pagebreak property of the range. It can be tested to see if a
pagebreak exists

if rows(6).pagebreak = xlNone then
‘No pagebreak
Else
‘ Has pagebreak
if rows(6).pagebreak = xlPageBreakAutomatic then
‘Automatic pagebreak
elseif rows(6).pagebreak = xlPageBreakManual then
‘ Manual pagebreak
End if
End if

Combining the above gives:

Sub Tester1()
Dim horzpbArray()
Dim verpbArray()
Dim brkType As String
ThisWorkbook.Names.Add Name:=»hzPB», _
RefersToR1C1:=»=GET.DOCUMENT(64,»»Sheet1″»)»
ThisWorkbook.Names.Add Name:=»vPB», _
RefersToR1C1:=»=GET.DOCUMENT(65,»»Sheet1″»)»
i = 1
While Not IsError(Evaluate(«Index(hzPB,» & i & «)»))
ReDim Preserve horzpbArray(1 To i)
horzpbArray(i) = Evaluate(«Index(hzPB,» & i & «)»)
i = i + 1
Wend
ReDim Preserve horzpbArray(1 To i — 1)
Debug.Print «Horizontal Pagebreaks (rows):»
For j = LBound(horzpbArray, 1) To UBound(horzpbArray, 1)
If Rows(horzpbArray(j)).PageBreak = xlNone Then
brkType = «None»
Else
‘ Has pagebreak
If Rows(horzpbArray(j)).PageBreak = xlPageBreakAutomatic Then
brkType = «Automatic»
ElseIf Rows(horzpbArray(j)).PageBreak = xlPageBreakManual Then
brkType = «Manual»
Else
brkType = «Unknown»
End If
End If

Debug.Print j, horzpbArray(j), brkType
Next j

i = 1
While Not IsError(Evaluate(«Index(vPB,» & i & «)»))
ReDim Preserve verpbArray(1 To i)
verpbArray(i) = Evaluate(«Index(vPB,» & i & «)»)
i = i + 1
Wend
ReDim Preserve verpbArray(1 To i — 1)
Debug.Print «Vertical Pagebreaks (columns):»
For j = LBound(verpbArray, 1) To UBound(verpbArray, 1)
If Columns(verpbArray(j)).PageBreak = xlNone Then
brkType = «None»
Else
‘ Has pagebreak
If Columns(verpbArray(j)).PageBreak = xlPageBreakAutomatic Then
brkType = «Automatic»
ElseIf Columns(verpbArray(j)).PageBreak = xlPageBreakManual Then
brkType = «Manual»
Else
brkType = «Unknown»
End If
End If

Debug.Print j, verpbArray(j), brkType
Next j
End Sub

Sample Output:
Horizontal Pagebreaks (rows):
1 13 Manual
2 24 Manual
3 39 Manual
4 67 Manual
5 87 Manual
6 114 Automatic
Vertical Pagebreaks (columns):
1 2 Manual
2 6 Automatic