Solved: Remove section break at end of file

[clhare]

I have a template that contains several sections. Some of the sections may or may not be included, so I have a bookmark created for each section that may be deleted, and a macro will delete the sections that are not needed based on selections in a userform.

That works fine, but I always end up with a blank section at the end of the document. Is there a way to remove the last section in the document if there's nothing in it? Am I doing something wrong?

I've attached a sample file.

Thanks for your help!

[fumei]

1. Cheryl, Cheryl, Cheryl. I would think with the posts you have had here you would be using Option Explicit. Go fix that right now. I'll wait......In the VBE, Tools > Options and check the Require Variable Declaration checkbox.

Done it? No? I'll wait......

2. Bleech bleech, all those cboWorktate.AddItem lines. Make an array, and load the array. Like this:Dim strStateNames
Dim var
' Assign the current date as the default in the date field
' (Format for date is set in field's Custom Format property)
dtpRequestDate = Date
dtpStartDate = Date
dtpEndDate = Date

' _____________________________________________________________________
' Insert state names on form (states that have an attachment in document)
' _____________________________________________________________________

strStateNames = Array("Select applicable state:", _
"Alabama", "Alaska", "Arizona", "Arkansas", _
"California", "Colorado", "Connecticut", _
"Delaware", "District of Columbia", "Florida", _
"Georgia", "Hawaii", "Idaho", "Illinois", _
"Indiana", "Iowa", "Kansas", "Kentucky", _
"Louisiana", "Maine", "Maryland", _
"Massachusetts", "Michigan", "Minnesota", _
"Mississippi", "Missouri", "Montana", _
"Nebraska", "Nevada", "New Hampshire", _
"New Jersey", "New Mexico", "New York", _
"North Carolina", "North Dakota", "Ohio", _
"Oklahoma", "Oregon", "Pennsylvania", _
"Puerto Rico", "Rhode Island", "South Carolina", _
"South Dakota", "Tennessee", "Texas", "Utah", _
"Vermont", "Virginia", "Washington", _
"West Virginia", "Wisconsin", "Wyoming")

For var = 0 To 52
cboWorkState.AddItem strStateNames(var)
Next

3. Bleech, bleech. All those If statements...If strWorkState <> "California" Then
If ActiveDocument.Bookmarks.Exists("California") = True Then
ActiveDocument.Bookmarks("California").Select
Selection.Delete
End If
End If
If strWorkState <> "Connecticut" Then
If ActiveDocument.Bookmarks.Exists("Connecticut") = True Then
ActiveDocument.Bookmarks("Connecticut").Select
Selection.Delete
End If
End If
If strWorkState <> "Iowa" Then
If ActiveDocument.Bookmarks.Exists("Iowa") = True Then
ActiveDocument.Bookmarks("Iowa").Select
Selection.Delete
End If
End If
If strWorkState <> "Kansas" Then
If ActiveDocument.Bookmarks.Exists("Kansas") = True Then
ActiveDocument.Bookmarks("Kansas").Select
Selection.Delete
End If
End If
If strWorkState <> "Kentucky" Then
If ActiveDocument.Bookmarks.Exists("Kentucky") = True Then
ActiveDocument.Bookmarks("Kentucky").Select
Selection.Delete
End If
End If
If strWorkState <> "Louisiana" Then
If ActiveDocument.Bookmarks.Exists("Louisiana") = True Then
ActiveDocument.Bookmarks("Louisiana").Select
Selection.Delete
End If
End If
If strWorkState <> "Massachusetts" Then
If ActiveDocument.Bookmarks.Exists("Massachusetts") = True Then
ActiveDocument.Bookmarks("Massachusetts").Select
Selection.Delete
End If
End If
If strWorkState <> "Minnesota" Then
If ActiveDocument.Bookmarks.Exists("Minnesota") = True Then
ActiveDocument.Bookmarks("Minnesota").Select
Selection.Delete
End If
End If
If strWorkState <> "Montana" Then
If ActiveDocument.Bookmarks.Exists("Montana") = True Then
ActiveDocument.Bookmarks("Montana").Select
Selection.Delete
End If
End If
If strWorkState <> "New Hampshire" Then
If ActiveDocument.Bookmarks.Exists("NewHampshire") = True Then
ActiveDocument.Bookmarks("NewHampshire").Select
Selection.Delete
End If
End If
If strWorkState <> "New Jersey" Then
If ActiveDocument.Bookmarks.Exists("NewJersey") = True Then
ActiveDocument.Bookmarks("NewJersey").Select
Selection.Delete
End If
End If
If strWorkState <> "Ohio" Then
If ActiveDocument.Bookmarks.Exists("Ohio") = True Then
ActiveDocument.Bookmarks("Ohio").Select
Selection.Delete
End If
End If
If strWorkState <> "Rhode Island" Then
If ActiveDocument.Bookmarks.Exists("RhodeIsland") = True Then
ActiveDocument.Bookmarks("RhodeIsland").Select
Selection.Delete
End If
End If
If strWorkState <> "Tennessee" Then
If ActiveDocument.Bookmarks.Exists("Tennessee") = True Then
ActiveDocument.Bookmarks("Tennessee").Select
Selection.Delete
End If
End If
If strWorkState <> "Washington" Then
If ActiveDocument.Bookmarks.Exists("Washington") = True Then
ActiveDocument.Bookmarks("Washington").Select
Selection.Delete
End If
End If
If strWorkState <> "Wisconsin" Then
If ActiveDocument.Bookmarks.Exists("Wisconsin") = True Then
ActiveDocument.Bookmarks("Wisconsin").Select
Selection.Delete
End If
End IfSelecting and deleting them...bleech. AND you do not even have them all - see next point.

You want to delete all the sections that are NOT the selected state, correct?Private Sub cmdOK_Click()
Dim oBM As Bookmark
Dim strWorkState As String
' other stuff
strWorkState = cboWorkState.Value
For Each oBM In ActiveDocument.Bookmarks
If oBM.Name <> strWorkState Then
oBM.Range.Delete
End If
NextDone. All other bookmarks are deleted except for the one selected from the combobox.

5. You do not have them all. I selected Illinois from the combobox. Took a little while to figure out what the heck was going wrong. Ahem....there is no Illinois in the document. DO NOT DO THIS! Even for your own testing, do not do this. Or if you insist on doing something like this with yourself, do not pass this on to someone else.

The values in the combobox should match values they are going to action. As it was, Illinois was selected, but since there was no Illinois.....

Now, it is true you are doing a if Exists, but if what you actually want to do, is delete all bookmarks except the selected state....

6. Why are you unloading the userform before it is finished? I moved the Unload instruction to the end of the OK_Click.

7. Regarding the last Section break, you could do it a bit more fancy, but I simply went to end and deleted it using selection. Here is the code for the commandbutton - without all those If statements.Sub cmdOK_Click()
Dim oBM As Bookmark

Application.ScreenUpdating = True
Application.ScreenRefresh
Application.ScreenUpdating = False

' Sets up the error trap
On Error GoTo ErrHandle

' Declare variables
Dim strWorkState As String

' Assign values to variables
strWorkState = cboWorkState.Value

' Replace variables with user's text
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "(state)"
.Replacement.Text = strWorkState
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute Replace:=wdReplaceAll
For Each oBM In ActiveDocument.Bookmarks
If oBM.Name <> strWorkState Then
oBM.Range.Delete
End If
Next
With Selection
.EndKey Unit:=wdStory
.TypeBackspace
.Delete
End With
Unload frmMyForm
Exit Sub

You DO know that the code would run through ALL of those IF statements?

8. The warning message is cute, but all you have to do is click the Close button and you can edit, which is what I did. Updated file attached.

USE OPTION EXPLICIT.

[clhare]

Only some of the states will actually have a section of text that applies to them...most of the states will not. I was under the impression that the state name had to be inputed in the text (even if it doesn't have a special section later in the document. I've gone back to them and looks like I'll only need the states with their own section to populate the combo box. That makes it easier.

When I took out all the company info to set up my sample template, I must have took out too much. Oops! I'll fix option explicit (must have deleted) and the array (not too good at those yet). Your way of loading the combo box is so much better!

I know there's alot of If statements, but I couldn't think of any simpler way to do it. Knew there had to be, hence this post! :)

I really appreciate your help! This forum is a terrific learning tool!

I'll get cracking on these updates immediately! :whip

[clhare]

Just one problem encountered...

There are other bookmarks in the document that are unrelated to the states, so I can't just delete all other bookmarked text. Is there an easy way to delete text for just the 16 state bookmarks if not selected, leaving any other bookmarked text as is?

[fumei]

Ah, I was wondering that. Because yes, my code removes all of the bookmarks.

OK. Sure. You just have to be careful with your naming. The easiest way is simply prefix all the state bookmarks with something.

st_Arizona, st_California, st_Kansas etc.

Now you can change the code to: For Each oBM In ActiveDocument.Bookmarks
Select Case Left(oBM.Name, 3)
Case "st_"
If oBM.Name <> "st_" & strWorkState Then
oBM.Range.Delete
End If
Case Else
End Select
Next Of course you could append "st_" to the strWorkState, OR you could do that with the combobox items, OR .... there are a few other possibilities.

The point being is you need a way to identify the state named bookmarks, and any others.

It is all in the names.