Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Trim leading/ending spaces from selection in vba

3,959 views
Skip to first unread message

Cheese_whiz

unread,
Jun 1, 2007, 3:04:00 PM6/1/07
to
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

unread,
Jun 2, 2007, 2:34:00 PM6/2/07
to
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

unread,
Jun 2, 2007, 5:43:24 PM6/2/07
to
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
jmarci...@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org


Cheese_whiz

unread,
Jun 2, 2007, 11:21:00 PM6/2/07
to
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

unread,
Oct 7, 2010, 12:46:52 PM10/7/10
to
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

> On Friday, June 01, 2007 3:04 PM Cheesewhi wrote:

> 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


>> On Saturday, June 02, 2007 2:34 PM oldma wrote:

>> 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
>>
>> "Cheese_whiz" wrote:


>>> On Saturday, June 02, 2007 5:43 PM Jean-Guy Marcil wrote:

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

>>> 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
>>> jmarci...@CAPSsympatico.caTHISTOO
>>> Word MVP site: http://www.word.mvps.org


>>>> On Saturday, June 02, 2007 11:21 PM Cheesewhi wrote:

>>>> 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
>>>>
>>>> "Jean-Guy Marcil" wrote:


>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>> Nested IF Statement ? Excel 2007
>>>> http://www.eggheadcafe.com/tutorials/aspnet/195df521-46a8-4b2f-a6aa-dad1fb2c63d5/nested-if-statement--excel-2007.aspx

Jay Freedman

unread,
Oct 7, 2010, 8:32:52 PM10/7/10
to
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

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.

Message has been deleted
Message has been deleted
Message has been deleted

dedawson

unread,
Oct 7, 2010, 1:57:37 PM10/7/10
to
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

Message has been deleted
Message has been deleted
0 new messages