Trim leading/ending spaces from selection in vba

[Cheese_whiz]

Hi all,

I have a macro that selects the next/current sentence as a whole. I found
it on a post around here.

The problem is it often selects a space or two before or after the actual
sentence, including often selecting the blank line above or below the
sentence.

I've done a search (a few of them), and I can't find any way to remove those
leading/following spaces from the current selection. Anyone have any ideas?

Here's the one line macro for selecting the sentence:

Selection.Sentences(1).Select

It works well except for picking up spaces before or after the sentence. I
also tried modifying it like this:

Selection.Sentences(1).Select
Selection.MoveStartWhile Cset:=" ", Count:=wdForward
Selection.MoveEndWhile Cset:=" ", Count:=wdBackward

Thanks, in advance.
CW

 

[old man]

Hi,

This works -

Dim r1 As Range
Dim str1 As String

Selection.Sentences(1).Select

Set r1 = Selection.Range
str1 = Trim(r1.Text)

r1.Text = str1

Try to use range objects as much as possible - cleaner, faster,,,

old man

 

[Jean-Guy Marcil]

old man was telling us:
old man nous racontait que :

Hi,

This works -

Dim r1 As Range
Dim str1 As String

Selection.Sentences(1).Select

Set r1 = Selection.Range
str1 = Trim(r1.Text)

r1.Text = str1

Try to use range objects as much as possible - cleaner, faster,,,

This works, but it actually removes character form the document and does not
ultimately select the sentence which was, I believe, the OP's goal.

There may be all kinds of reasons why it selects the "Blank" lines above or
below: tab characters, spaces, binding spaces, paragraph marks, manual line
breaks, etc.
Use the "Show all" button to see what is going on (the ¶ button on the
standard toolbar). Here is an example that works in some cases, but not all.
By the way, I could not get it to select a blank line before, I guess I did
not hit on the right character combination.

With Selection
.Sentences(1).Select
.MoveStartWhile Cset:=" ", Count:=wdForward
.MoveEndWhile Cset:=Chr(13), Count:=wdBackward
.MoveEndWhile Cset:=" ", Count:=wdBackward
End With


Also, ma I suggest that you tell us your overall goal, there may be a better
way.
old man may be on the right track if ultimately you do not actually need to
select the actual sentence in the document, but just need the text content
fro processing elsewhere.

Oftentimes people post a question regarding a few line of code they need
help with, when in fact the overall process they are engaged in is less then
efficient...
I am not saying that this the case now... but one never knows!

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Cheese_whiz]

Thanks old man and Jean for the replies. I'll play with the code you both
provided but I'm new to word vba. I have some experience in access vba,
though.

Jean, what I want to do is just select the sentence from first letter (or
punctuation mark, in the case of a quote, for example) to last letter or
punctuation mark. The 'blank lines' reference was a poor choice of words.
What I meant was that if I type a line of text, hit return twice (leaving a
'blank line'), and then type another line of text and use the code I found
earlier, I end up selecting the line and the blank line (and if there is two
blank lines, usually both of them).

It's like the code considers a 'sentence' to be everything from the end of
the previous sentence (what I WANT to be the 'end' of the previous
sentence......read: just past the last typed character) across the sentence
being selected and all the way to the character just before the next sentence.

The bottom line is if I use the macro and, for example, delete the selected
sentence I'm also deleting paragraph transitions and necessary spaces. It's
not THAT big of a deal but just something I wanted to fix.

Thanks again for the help,
CW

 

[Christienne Mancini]

I have a similar situation, I'm trying to append characters at the end of an ID such that I can find it later in the code to create hyperlinks. I need to update the below script such that the ending space is deleted in the range in VBA but not in the Word document. The Word document currently looks like "The identifier is DT-198 number." and I want to change it to "The identifier is DT_198$%& number." I don't want the space after the ID to be deleted.

Dim wdrange As Word.Range
Dim para As Paragraph
Dim mystring As String

For Each para In ActiveDocument.Paragraphs
Set wdrange = para.Range
wdrange.Find.Execute FindText:="DT-", replacewith:="DT_"
wdrange.Find.MatchCase = True
If wdrange.Find.Found = True Then
wdrange.MoveEnd wdWord
mystring = Trim(wdrange.Text)
wdrange.Text = mystring
wdrange.InsertAfter "$%&"
End If
Next para

 

[dedawson]

Consider dropping the VBA and simply use Search-Replace with Wildcards

Search string: (DT-)([0-9]{1,5})( number)
Replace string: DT_\2$%&\3

Test data: Leading text DT-198 number trailing text

Replaced as: Leading text DT_198$%& number trailing text

Note:
Assumption is that the number after the DT- is purely numeric, and
that it has a maximum length of 5 digits. If longer, modify the
second digit of the {1,5}

 

[dedawson]

Consider dropping VBA and doing with a simple Find - Replace using
wildcards

Search String: (DT-)([0-9]{1,5})( number)

Replace string: DT_\2$%&\3

Test data: Leading text DT-198 number trailing text

Replaced as: Leading text DT_198$%& number trailing text

Note: Assumption is that the number that follows DT- is strictly
numeric and no greater than 5 digits. If longer, change the second
value of {1,5}

Also, be sure to check Use Wildcards in the Find and Replace dialog

 

[dedawson]

Consider dropping VBA and doing with a simple Find - Replace using
wildcards

Search String: (DT-)([0-9]{1,5})( number)

Replace string: DT_\2$%&\3

Test data: Leading text DT-198 number trailing text

Replaced as: Leading text DT_198$%& number trailing text

Note: Assumption is that the number that follows DT- is strictly
numeric and no greater than 5 digits. If longer, change the second
value of {1,5}

Also, be sure to check Use Wildcards in the Find and Replace dialog

 

[Jay Freedman]

You aren't looking at this the best way. You can get the result you want simply by replacing "DT-" with "DT_$%&". That way, the space doesn't get involved at all.

By the way, putting the MatchCase option in a separate statement after the .Execute call means that the find that executes will *not* be case-sensitive. Also, there's no need to loop through the
Paragraphs collection; just use the ReplaceAll option. Do it this way:

Sub x()
Dim wdrange As Range
Set wdrange = ActiveDocument.Range
wdrange.Find.Execute FindText:="DT-", ReplaceWith:="DT_$%&", MatchCase:=True, Replace:=wdReplaceAll
End Sub

 

[dedawson]

Consider dropping VBA and doing with a simple Find - Replace using
wildcards

Search String: (DT-)([0-9]{1,5})( number)

Replace string: DT_\2$%&\3

Test data: Leading text DT-198 number trailing text

Replaced as: Leading text DT_198$%& number trailing text

Note: Assumption is that the number that follows DT- is strictly
numeric and no greater than 5 digits. If longer, change the second
value of {1,5}

Also, be sure to check Use Wildcards in the Find and Replace dialog
Leading text DT_198$%& number trailing text

Tried posting this early this AM, but nothing would take

 

[dedawson]

Consider dropping VBA and doing with a simple Find - Replace using
wildcards

Search String: (DT-)([0-9]{1,5})( number)

Replace string: DT_\2$%&\3

Test data: Leading text DT-198 number trailing text

Replaced as: Leading text DT_198$%& number trailing text

Note: Assumption is that the number that follows DT- is strictly
numeric and no greater than 5 digits. If longer, change the second
value of {1,5}


Also, be sure to check Use Wildcards in the Find and Replace dialog

Leading text DT_198$%& number trailing text

 

[dedawson]

Consider dropping VBA and doing with a simple Find - Replace using
wildcards

Search String: (DT-)([0-9]{1,5})( number)

Replace string: DT_\2$%&\3

Test data: Leading text DT-198 number trailing text

Replaced as: Leading text DT_198$%& number trailing text

Note: Assumption is that the number that follows DT- is strictly
numeric and no greater than 5 digits. If longer, change the second
value of {1,5}

Also, be sure to check Use Wildcards in the Find and Replace dialog