Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Remove Pagebreaks

How can I remove the 'pagebreaks' on the active worksheet with VBA?

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Remove Or Adjust PageBreaks

I have a worksheet which I clear, then use the Copy method to copy from other sheets and paste into this blank one.

I set the PageSetup.FitToPageWide = 1, to set the page area, but i'm left with a vertical pagebreak, which I don't need.

I can't remove it. I've tried the DragOff, but that don't work. As far as I can see the VPageBreak object has a Delete method. But, I can't get that working either.

Any help?

Borders On Pagebreaks (VBA)
hi everybody,
is there a way to put in borders wherever pagebreaks exist using a macro?

Finding PageBreaks: Please Help!
I'm trying to return the locations of the automatic pagebreaks set by excel. I found the function VPageBreaks and used:

(and worksheets(1).vpagebreaks.item(1).location)

but I don't know how to store the value. Simply setting it equal to a variable doesn't work. The function definition says that it returns the locations, but all of the examples I've seen only show setting the locations.

If this isn't possible, another option is repeating lines at the top of the page, but I can't find the VBA command to do that. As far as I can tell, you can only do that manually through the file/setup/sheet menus. Please let me know if I'm wrong.

I would appreciate any help at all.

DataReport And Pagebreaks
I have now finished the datareports for my program, except for the report footers. If the report is less than a page it displays the footer right under the contents of the report.

How can I have my report footers displayed at the bottom of the report?

Excel PageBreaks
This is a VBA issue ........I have an Excel spreadsheet that has numerous lines of data per Branch Number......I want to insert a page break at the end of each different Branch Number so that different people can receive individual Branch information.

Using VBA code, how can i insert a pagebreak between different Branch Numbers ???
i.e Branch 1 on one page....Branch 2 on the next...etc,etc

All Branch Numbers are in column A

thanks, in advance

Excel PageBreaks.
Can anyone tell me the correct way to put a page break in an Excel Spreadsheet.

I've tried using:

VB Code:
xlsWSheet.HPageBreaks.Add.Range("A45") and xlsWsheet.Rows(45).PageBreak = xlPageBreakManual

When I check the spreadsheet there is no visible pagebreak inserted, but if I click on the cell and do 'Insert' the 'Remove PageBreak' option is there. If I try printing it ignores the inserted pagebreak.


VB - Excel PageBreaks
I am producing an excel worksheet from VB. Is it possible to clear the worksheet of all pagebreaks and if so, how? I can insert my own pagebreaks but would like to delete them all before doing so.
Thanks in advance.

Print A File/pagebreaks
I would like to know how I can print a file that I have created and written to in VB. It is just a simple .txt or .doc file. I would also like to know how you place a page break in the file. At present I am using the following code:

Open FileName For Output As #1 'FileName is determined earlier

print #1, Then a whole lot of information

print #1, Then more info.

close #1

Each time this program runs the information stored in the file is exactly the same length. I would like to be able to place page breaks in order to allow the file to be printed correctly (it has tables that I want to start at the top of the page.

Any suggestions would be appreciated


Pagebreaks On Excel Datasource
Im using Data Reports and i need help on how to write a code for stopping or pagebreaking the next Trans No into the next page.. i got a sample xls file that u can see so that u may know when to stop or pagebreak..

heres the link: BATCH.xls

PageBreaks In Excel - RESOLVED
I'm setting the 'FitToPagesTall' property to enable paebreaks to be inserted into my Excel document (which is the output for my VB project), this was advised on a previous thread, which seemed to work OK.
However, I've experienced a problem today which I hope someone can shed some light on........

There are 4 different ways I have run my project and only 3 work :

1. My Company login a/c on a windows 98 machine......OK
2. A Colleagues Company login a/c on a windows 98 machine......OK
3. My Company login a/c on a windows XP machine......NOT OK
4. A Colleagues Company login a/c on a windows XP machine......OK

when number 3 fails, it returns a run time error complaining about the fact that i'm trying to 'set the FitToPagesTall property' ???

anyone else seen this ?

thank you very advance

ps.....i'm setting the property to FALSE, if this makes a difference

How To Make PageBreaks In Vbscript For Excel ?
Hye everybody,
I am trying to insert Horizontal page breaks in special places... would someone now how to do it ???

In excel macro it is :
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

But how to put it in vb... I tried several ways but
These doesn't work:
Code: Set Excel = CreateObject("Excel.Application")
Set xlBook = Excel.Workbooks.Add
Set xlSheet = xlBook.WorkSheets(1)

xlSheet.HPageBreaks.Add .Range("A27")

I am waiting for some answers... Tchao

Control Pagebreaks In Excel Macro - SOLVED

I've got a problem with how to control pagebreaks on a sheet with a macro.
I tried to use
ActiveSheet.HPageBreaks.Add before:=Range...
and the probelm with this is that it leaves the automatic pagebreaks on.

Then I tried to move the exsisting pagebreaks with locating them by
t(i) = ActiveSheet.HPageBreaks(i + 1).Location.Row (-> not the best cause the window needs to be scrolled otherwise it generates 'Subscript out of range' error)

then defined the new location by
ActiveSheet.HPageBreaks(i + 1).Location=Range(..)
the problem here was that either the pbreak remained or it moved but deleted the content of the origin cell.

then i tried to delete pbreaks by
setting Cells.Pagebreak=xlPageBreakNone which also left the automatic pbreaks on the sheet.
But didnT work.

Any suggestions?


Edited by - Apax on 10/25/2004 9:08:30 AM

Why Can I Remove A User From A Group But I Can't Remove A User
HI thanks for looking at this post

I am able to programmatically remove a user and add a user and
set a password to anything i like in the System database from VB
but i can't find out how to delete a user completely does anyone
know how?

The following code will remove a user from a group
but not delete the User completely...


' Procedure : DAORemoveUser
' DateTime : 14,Nov,2003 22:50
' Author : Steven Renoir
' Purpose :
Public Sub DAORemoveUser(strUserName As String, strGroupName As String)

On Error GoTo DAORemoveUser_Error
Dim gName As String, _
U As User

Set U = DBEngine.Workspaces(0).Users(strUserName)
gName = U(strGroupName).Name
If gName = strGroupName Then
U.Groups.Delete strGroupName
MsgBox strUserName & " does not exist in " & strGroupName
End If

Exit Sub

If Err <> 3265 Then
MsgBox _
"Error " & Err.Number & " (" & Err.Description & ") has occured " _
& vbCrLf & "in the DAORemoveUser procedure of Module Security" _
& vbCrLf & " in the interests of making the program bug free" _
& vbCrLf & vbCrLf & " Please Email Software support at" _
, vbExclamation, "Please Write Down This Error Message!"
WriteError Err.Number, Erl, Err.Description, "Security Module", "DAORemoveUser"
Resume NoUserFound
End If
Resume Exit_DAORemoveUser
End Sub

This code is ADO but i am not sure how to use it
seeing as i am using DAO in my App
Private Function DeleteUser(ByVal strUser As String) As Boolean
    Dim catDB As ADOX.Catalog

    On Error GoTo DeleteUser
    ' Instantiate the Catalog object.
    Set catDB = New ADOX.Catalog
    With catDB
        ' Open Catalog object in current database.
        .ActiveConnection = CurrentProject.Connection
        ' Delete strUser.
        .Users.Delete strUser
    End With

    ' Close Catalog object.
    Set catDB = Nothing

    DeleteUser = True

    Msgbox Err.Number & ":" & Err.Description
    DeleteUser = False
End Function

Ah! here it is at last!!!


Public Sub DAODeteUser(StrUser As String, strGroup As String)

   On Error GoTo DAODeteUser_Error
Set U = DBEngine.Workspaces(0).Users(StrUser)

DBEngine.Workspaces(0).Users.Delete StrUser

   Exit Sub


   MsgBox _
        "Error " & Err.Number & " (" & Err.Description & ") has occured " _
        & vbCrLf & "in the DAODeteUser procedure of Module Security" _
        & vbCrLf & " in the interests of making the program bug free" _
        & vbCrLf & vbCrLf & " Please Email Software support at" _
        , vbExclamation, "Please Write Down This Error Message!"
 WriteError Err.Number, Erl, Err.Description, "Security Module", "DAODeteUser"

Resume Exit_DAODeteUser
End Sub



Edited by - binduau on 11/14/2003 4:14:07 PM

Why &"remove&" Method Can Not Remove Controls Added Using &"add&" Method?
I use "add" method to dynamically add some controls in the form, then I use "remove" method to remove them, but it doesn't work!
My code is like following.

Private WithEvents Command1 As CommandButton

Private Sub Command1_click()
' some codes to be executed when Command1 is clicked
End Sub

Private Sub AddControl()
Set Command1 = Controls.Add("VB.CommandButton", "Command1")
With Command1
.Visible = True
.Caption = "Command1"
.left = 2000
.top = 1000
.width = 500
.height = 200
End Sub

Private Sub RemoveControl()
Controls.Remove "Command1"
End Sub

When AddControl() is executed, a command button named "Command1" is added to the form. But when RemoveControl() is executed, there is error, and the
error message is "Run-time error '365': unable to unload
within this context". Why does this happen? Thanks.

Remove Row
Could someone help me solve a little problem I'm having. I have a form with a MSFlexGrid that the user can add info to one row at a time from a text box and 3 combo boxs. I would like to have a button allowing the user to clear a row if they make a mistake. What I have now keeps bring up an err saying that I can not RemoveItem a fixed row. This is what I've go so far. Thanks for any help.

Private Sub ClearLast_Click()

With grdSchedule
If .Rows <> 2 Then
.RemoveItem intRow
End If
End With
End Sub

Private Sub grdSchedule_Click()
intRow = grdSchedule.Row
End Sub

Remove Right 1
Well this one really has me stumped. I have a string of "C:New" and I basically want to remove the right "" to make it "C:New".
There's gotta be a simple way of doing this..?

Any ideas?

How To Remove
hi, how to remove special character such as enter in database using vb6 so that when I create filesytemObject is should not be included


Remove A Tab
This is another silly question, but I can't figure it out.. I am using a Tab control,, I had 3 tabs at one point, but now I only want 2, I don't see anywhere to remove a tab, it is the last one, so should be easy.. If I reset the tab index number, I get "Invalid Property Value".. I don't want this done in code, I found that, but set permanently in application... Thanks so much In case it matters it is SSTab control.

i have lines like in one textbox

askjn mncvh ihdmnvkh ABC fdjdkfmk mv fkj
v kjmlkvjlk ABC mnjdfhkjdf kjhdsjfh
kjdfakj adfk lkHTjlkaf lkjlkaf lkjlkadf lkjlkafl ABC
m nmn jk jlkj lkjlkpoudfv lkjlkjvkjllllllllllll ABC fgHTRElkgfi
LGK GKdfsgb kfdsglkjljsglkjlsg

i need to remove any words located after "ABC"

Remove XX/100 From CR
How to remove XX/100 from Crystal Report whern use TOWORDS(1000).
I want to write ONLY instead of XX/100
Please help me

Remove DSN

How to remove DSN using VB programme.Databse is in MS Acess


Remove The &lt; And The &gt;

While formatting Text in a Richtextbox I do this:

With rtbAllg
.Visible = False
sPos = 0
sPos = .Find("<", sPos + 1)
If sPos > 0 Then
ePos = .Find(">", sPos + 1)
If ePos > 0 Then
.SelStart = sPos
.SelLength = ePos - sPos + 1
.SelColor = vbBlue
.SelBold = True
sPos = ePos + 1
End If
End If
Loop Until sPos < 1
.Visible = True
End With

Works fine, but now:
Now I just want to remove the ugly control characters like this: < and this: >

Any Ideas?

Thanks in Advanced,


Is there a way to remove something like rep() replaces?

ie. part of a string etc

I'm trying to remove the Left(string, 1) bit of a string and I'm not sure how

Remove Pt2
How would I remove the LAST character in a string?

Remove Tab

i wanna read a file line by line and save it in a variable
and then i want to remove all tabs in front of that line, but i dont know how to remove tabs (vbTab)? who can help me?

Remove Help
VB Code:
for each lst in lstview.ListItems With lstView  If .SelectedItem.Checked = True Then    .ListItems.Remove(.SelectedItem.Index)  End IfEnd WithNext

does the above remove all the checked rows in the listview if i assign it to a cmd and clicked it??
i tried but it does does anyone has any ideas on how to remove those checked records on a listview using a loop or anything at a click of a cmd button?

what is the function for removing all the spaces from a string??

I need to be able to remove the double quotes from a text file.

I am writiing a string to a text file but I dont want the quotes to be around it.

Please help me.

Example "Help" should be Help

How Can We Remove A Dsn By Using Vb
Hello friends,

Is there any way to remove a dsn created by using the
rdoregisterdatasource in vb. Please help me in this problem.

Thanks in Advance


How To Remove VSS?
How can i uninstall the Visual Source safe from my computer?
am looking for this application in Control PanelAdd or Remove Progrms in oreder to remove, but i dont see it.

Thanks for your help.

Remove Key From INI?

Anyone point me in the right direction? I want to remove a key and its value from an ini file on command1_click() event?

Thanx in advance

Kind Regards - Trvo

I suport public educasion.

How can I automatically remove my program through a VB program? so I can install an update.

Is There Anyway To Remove.....
After .exe is made ......(when you right-click on the .exe and select "properties")....well is there anyway to remove "Version" tab in properties of a program

Thank You

ADO - Remove Row
In ADO, when I try to insert a new line, I use the command "AddNew", now if I want to remove a line, what command shall I use?


Remove Ini Sections

How do I go about REMOVING sections from an Ini File?

This is for a little game I'm making. I have profiles for each character in an Ini format


etc etc

It's easy to read/write all of it, but how about if i wanted to delete a whole char. How do I go about doing this?

Thanks in advance

Bit Of List/Remove Help
listDeleteCat.RemoveItem (listDeleteCat.Text)
Open "C:Program FilesDemoSuite Admin-PaneldataSeries.txt" For Output As #1
Print #1, listDeleteCat
Close #1
'Fills the category combobox with all the categories in the Delete Category section
With listDeleteCat
Open "C:Program FilesDemoSuite Admin-PaneldataSeries.txt" For Input As #1
While Not EOF(1)
Line Input #1, buf$
.AddItem (buf$)
End With
Close #1
I get the error "Type Mismatch" on the follow line

listDeleteCat.RemoveItem (listDeleteCat.Text)
Basically what its supposed to do is "remove" the item I have selected on the list, then save the data. The refresh the data

What am I doing wrong, and if you know please leave a few tips.

Remove The Control Box
This should be an easy one, I think. Can anyone tell me how I can remove the control box in the upper left hand corner of my forms and still show the min/max buttons in the upper right corner? Thanks!

Bkg On Form How To Remove
i got a program of psc and it has a background on the form i want to remove but cant seem to could you talk a look and try to help me remove it here is the program

Remove VBcomponents ?

I've created this VBA code to import a module and an userform

Sub autonew()
ActiveDocument.VBProject.References.AddFromFile _
"C:Program FilesCommon FilesSystemadomsado25.tlb"
ActiveDocument.VBProject.VBComponents.Import _
ActiveDocument.VBProject.VBComponents.Import _
ActiveDocument.saveas "c: emp emp.doc" "c: emp emp.doc"
End Sub

Sub AutoOpen()
End Sub

When a user pushes on the OK Button in userform1 a new file will be created. Becuase I create that file with
ActiveDocument.saveas "c:communicationfax01.doc"
I also has the imported VBcomponets.

I want to know how I can remove/delete the VBcomponents so that when I open the file later it doesn't show the userform

Greetings Raymond

Remove/add Macro
Four months ago, I added a macro, Insert Grid, that I wrote to (Word, office XP). Now, I cannot remember how I added it.
Also, I modified the macro and now I want to remove the old macro and add the new macro.

#1 I have a word doc called InsertGrid. When I open it I can go into VB (alt F11) and see, modify and save my macro.

#2 What are the steps to delete/remove the previous version of Insert Grid and get the new version of my macro Insert Grid in this word doc into


Cannot Remove All Vba References

I hope some one can help as this is driving me nuts!

I have a series of xls's that I need to mail out to clients, the sheets are generated from a master workbook, saved as a seperate workbook, then have all vba code removed from them, to allow them to pass through firewalls etc.

The problem I'm getting is : All the VBA code appears to be removed, but when I examine the created xls through notepad, I can still see vba code references, when I examine the xls through excel I can see no macros, no vba nada.

Unfortunately I can't just copy the sheets into anew workbook, for all sorts of reasons, chart objects , 255 character limits etc.

So I really need to be able to strip out all vba code (The version of excel is 9)

Any ideas would be really, really appreciated.



Note :

Attached is an example of one of the created reports, and also below is the sub I'm using to try and remove vba code from it:

Sub RemoveAllCode()

Dim VBComp As Object, AllComp As Object, ThisProj As Object, AllRef As Object
Dim ThisRef As Object, WS As Worksheet, DLG As DialogSheet
If ActiveWorkbook.Name <> ThisWorkbook.Name Then
Set ThisProj = ActiveWorkbook.VBProject
Set AllComp = ThisProj.VBComponents
Set AllRef = ThisProj.References

For Each VBComp In AllComp
With VBComp
Select Case .Type
Case vbext_ct_StdModule, vbext_ct_ClassModule, _
AllComp.Remove VBComp
Case vbext_ct_Document
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End Select
End With
For Each ThisRef In AllRef
If Not ThisRef.BuiltIn Then ThisProj.References.Remove ThisRef
End If
Application.DisplayAlerts = False
For Each WS In Excel4MacroSheets
For Each DLG In DialogSheets

Edit: Please use [vb] tags [/vb ]

How To Remove References?
I am trying to create a report which from a fairly complex workbook makes a small values-only sheet. I have managed to remove all obsolete sheets and all macros (including the one doing it all) but can not find a way to remove references. References are created by using VBE tools, references, and can be manually removed that way. What I need is a way to code this as I don't want my report to have any macros (references come up as macros with a disable/enable prompt on open). I have spent quite some time searching for an answer, examples provided with Access library do not work, type "Reference" is not recognised?!?
I can e-mail you the file if you like (too big to post here).

For more clarity, what I want to remove is (looking in project manager in VBE): there is VBAProject (myfile.xls) and it consists of MSE objects (sheets), Modules (macros) and References (ones I want to remove).


Add/remove A Field Example
can someone reply w/ an example of adding/removing a field from a table?

Remove Mailmerge
Can anyone help,

I have a vb6 app that opens word documents for editing.
These word docs are linked to a .dat file via mailmerge.

What i am trying to find out is if there is a way to remove the mailmerge
link threw vb code.

i.e. i want to use the mailmerge if it is a new doc, but not if i am editing the doc.

thanks in advance.


Remove A New Line?!

How do i remove a new lime? (vbCrlf / vbNewLine) - i dont know what it is..

its a little square ind the text like [] but if you copy/paste it its making a new line.. how do i remove it ?

Remove Xml Tags
ok here's what i have, my form has a webbrowser that does the following.

Private Sub cmdCheck_Click()
webFriend.Navigate "" + txtFriend.Text + "?xml=1"
End Sub

txtFriend.Text is the account name they enter, it's for an online game to see if a player is online, now the page returns xml format like <account>yourname<account>
How would i make the webbrowser remove all the xml formatting and just return the value between the tags?
Any help is appreciated and thanks in advance.

All I want it to do is grab the page which will be random depending on what is entered in txtFriend.Text and remove all of the xml formatting such as <nickname>Project Glitch<nickname/> should only show Project Glitch.

Date Remove?
i know you can use DateAdd function but is there anycode to remove days from a date?

Duplicate Remove
i have a duplicate remove but what i want to do is if i have to listbox with listbox 1 with 1234 and 123 in it and also listbox 2 with 1234 and 342 in it how can i get it so it will find the 1234 in listbox 2 and remove it?

Remove Last Line
Hello everyone...

I have a utility that creates a text file with the results of a query. Unfortunatly the process is adding a blank line at the end of the files.

Can anyone tell me how to re-open the file after it is done and delete the last line, or tell me what in my code is causing this?

'Put results from query in string
OutStr = rs.GetString(adClipString, , ",", vbCrLF, vbNullString)

'Open Output File to Save
Open OutFile2 For Output As #1
Print #1, OutStr
Close #1

Copyright 2005-08, All rights reserved