Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-17-2017, 01:48 PM
Tye30 Tye30 is offline Delete Character on Single Line Only Windows 7 32bit Delete Character on Single Line Only Office 2010 32bit
Novice
Delete Character on Single Line Only
 
Join Date: Apr 2017
Posts: 7
Tye30 is on a distinguished road
Default Delete Character on Single Line Only

Hello All,



I have made several macros using the Macro Recorded language and I am looking for help to learn and update my macros to a more efficient style, mainly to speed up the macros.



Code:
 
 Sub TEST_REVISE()
 
    Selection.HomeKey Unit:=wdLine:
    Selection.MoveRight Unit:=wdCharacter, Count:=5, Extend:=wdExtend:
    Selection.TypeBackspace:
    Selection.MoveRight Unit:=wdCharacter, Count:=1:
    Selection.MoveRight Unit:=wdCharacter, Count:=3, Extend:=wdExtend:
    Selection.TypeText Text:="R":
    Selection.MoveRight Unit:=wdCharacter, Count:=8: Selection.TypeBackspace:
    Selection.MoveRight Unit:=wdCharacter, Count:=15: Selection.TypeBackspace:
    Selection.MoveRight Unit:=wdCharacter, Count:=7: Selection.TypeBackspace:
    Selection.MoveRight Unit:=wdCharacter, Count:=6: Selection.TypeBackspace:
    Selection.MoveRight Unit:=wdCharacter, Count:=15: Selection.TypeBackspace:
    Selection.MoveRight Unit:=wdCharacter, Count:=7: Selection.TypeBackspace:
    Selection.MoveRight Unit:=wdCharacter, Count:=6: Selection.TypeBackspace:
    Selection.MoveRight Unit:=wdCharacter, Count:=4: Selection.TypeBackspace:
    Selection.MoveRight Unit:=wdCharacter, Count:=4: Selection.TypeBackspace:
    Selection.MoveRight Unit:=wdCharacter, Count:=3: Selection.TypeBackspace
    Selection.MoveRight Unit:=wdCharacter, Count:=7: Selection.TypeBackspace:
    Selection.MoveRight Unit:=wdCharacter, Count:=13: Selection.TypeBackspace:
    Selection.MoveRight Unit:=wdCharacter, Count:=16: Selection.TypeBackspace:
    Selection.MoveDown Unit:=wdLine, Count:=1:
    Selection.HomeKey Unit:=wdLine
End Sub
I need it to
1. Delete all instances of this character "|" on the line
2. Add the character "R" after the "Z"
3. Delete the whitespace or collapse the text down to the beginning of the line

Example Before:

----|Z| | 1111 |2222222 22|3333 |44444|55555 5|6666 |77777|A |BBB| |CCC | | 111A1111-1111 |


Example After:


ZR 1111 2222222 223333 4444455555 56666 77777A BBB CCC 111A1111-1111


- is for character count of 4 of whitespace

Any help would be greatly appreciated! When running this macro for 1000+ lines it can take a while.

Enjoy,

Tyler
Reply With Quote
  #2  
Old 04-17-2017, 05:36 PM
macropod's Avatar
macropod macropod is offline Delete Character on Single Line Only Windows 7 64bit Delete Character on Single Line Only Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Assuming these lines start a new paragraph, you could just use Find/Replace, where:
Find = ^p |Z
Replace = ^pZR
followed by:
Find = |
Replace = nothing
If you need to also eliminate any unwanted duplicate spaces, you could use
Find = ^w
Replace = ^32
Of course, all three Find/Replace operations could be recorded as a macro.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 04-18-2017, 11:17 AM
Tye30 Tye30 is offline Delete Character on Single Line Only Windows 7 32bit Delete Character on Single Line Only Office 2010 32bit
Novice
Delete Character on Single Line Only
 
Join Date: Apr 2017
Posts: 7
Tye30 is on a distinguished road
Default

Hello macropod,

Thank you for responding to my question! This is what I came up with from your suggestion

Code:
 
Sub TEST_REVISE()
'
'
    Selection.HomeKey Unit:=wdLine
 
    With Selection.Find
        .Replacement.ClearFormatting
        .Text = "^p    |Z| "
        .Replacement.Text = "^pZR"
        .MatchWildcards = False
        .Execute Replace:=wdReplaceOne, Forward:=True, Wrap:=wdFindContinue
    End With
    With Selection.Find
        .Replacement.ClearFormatting
        .Text = "|"
        .Replacement.Text = ""
        .Execute Replace:=wdReplaceOne
    End With
 
        Do While Selection.Find.Found = True And iCount < 13
        iCount = iCount + 1
            With Selection.Find: .Execute Replace:=wdReplaceOne, ReplaceWith:="", _
                     Forward:=True: End With
            'myNumber = myNumber + 1
 
        Loop
 
    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.HomeKey Unit:=wdLine
 
End Sub
When I run the macro it doesn't actually find the first instant of "^p |Z| " it will skip the line the cursor is on and actually find the line below it. Also, it runs a bit slower at about 26 seconds to execute 100 times vs 3 seconds to execute 100 times.
Reply With Quote
  #4  
Old 04-18-2017, 03:58 PM
macropod's Avatar
macropod macropod is offline Delete Character on Single Line Only Windows 7 64bit Delete Character on Single Line Only Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Try:
Code:
Sub Demo()
Application.ScreenUpdating = False
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchWildcards = True
    .Text = "[ ]{4}|Z|"
    .Replacement.Text = "ZR"
    .Execute Replace:=wdReplaceAll
    .Text = "|"
    .Replacement.Text = ""
    .Execute Replace:=wdReplaceAll
    .Text = "[ ]^13"
    .Replacement.Text = "^p"
    .Execute Replace:=wdReplaceAll
  End With
End With
Application.ScreenUpdating = True
End Sub
On my laptop, with 10,000 records to process, execution takes just 21 seconds.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 04-19-2017, 06:40 AM
Tye30 Tye30 is offline Delete Character on Single Line Only Windows 7 32bit Delete Character on Single Line Only Office 2010 32bit
Novice
Delete Character on Single Line Only
 
Join Date: Apr 2017
Posts: 7
Tye30 is on a distinguished road
Default

Thank you again macropod for continuing to help me! I want to apologize because I think I mislead you in my last post. This macro has to be for a single line and cannot be done to an entire document. Yes, this macro will be used 1000+ times in my document, but not for every line so a replace all would not work.
Reply With Quote
  #6  
Old 04-19-2017, 12:27 PM
macropod's Avatar
macropod macropod is offline Delete Character on Single Line Only Windows 7 64bit Delete Character on Single Line Only Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Tye30 View Post
I think I mislead you in my last post.
Indeed. Try:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim StrTmp As String
With Selection
  .Start = .Paragraphs.First.Range.Start
  .End = .Paragraphs.First.Range.End - 1
  StrTmp = Trim(Replace(Replace(.Text, "|Z|", "ZR"), "|", ""))
  Do While InStr(StrTmp, "  ") > 0
    StrTmp = Replace(StrTmp, "  ", " ")
  Loop
  .Text = StrTmp
End With
Application.ScreenUpdating = True
End Sub
With this code, it will process any paragraph you click in - you don't have to select the entire paragraph beforehand
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 04-20-2017, 09:49 AM
Tye30 Tye30 is offline Delete Character on Single Line Only Windows 7 32bit Delete Character on Single Line Only Office 2010 32bit
Novice
Delete Character on Single Line Only
 
Join Date: Apr 2017
Posts: 7
Tye30 is on a distinguished road
Thumbs up

macropod, thank you! This is great! I just had to tweak it a little bit, but this what I was hoping for

Code:
 
    Dim StrTmp As String
    
    With Selection
        .Start = .Paragraphs.First.Range.Start
        .End = .Paragraphs.First.Range.End - 1
         StrTmp = Trim(Replace(Replace(.Text, "L31", "@@@"), "|", ""))
        'Do While InStr(StrTmp, "  ") > 0
        ' StrTmp = Replace(StrTmp, "  ", " ")
        'Loop
        .Text = StrTmp
    End With
    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.HomeKey Unit:=wdLine
Reply With Quote
  #8  
Old 04-20-2017, 04:56 PM
macropod's Avatar
macropod macropod is offline Delete Character on Single Line Only Windows 7 64bit Delete Character on Single Line Only Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Tye30 View Post
macropod, thank you! This is great! I just had to tweak it a little bit, but this what I was hoping for

Code:
...
         StrTmp = Trim(Replace(Replace(.Text, "L31", "@@@"), "|", ""))
...
I don't know how anyone was supposed to come up with that from anything you'd previously posted on the matter. What you're doing here is entirely different from what you previously said you wanted to do.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 04-20-2017, 08:40 PM
Tye30 Tye30 is offline Delete Character on Single Line Only Windows 7 32bit Delete Character on Single Line Only Office 2010 32bit
Novice
Delete Character on Single Line Only
 
Join Date: Apr 2017
Posts: 7
Tye30 is on a distinguished road
Default

Ha ha...Yeah, I got a little excited and was already starting to incorporate your code into my other macros and I ended up posting the wrong one. Thank you again macropod, I am new to VBA so I appreciate you helping me!


Code:
 
Sub LINE_REVISE()
'
'
Dim StrTmp As String
With Selection
  .Start = .Paragraphs.First.Range.Start
  .End = .Paragraphs.First.Range.End - 1
  StrTmp = Trim(Replace(Replace(.Text, "|Z| ", "ZR"), "|", ""))
  .Text = StrTmp
End With
    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.HomeKey Unit:=wdLine
End Sub
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete Character on Single Line Only Every page indents first line one character glennhardy Word 6 02-05-2016 04:05 PM
Delete Character on Single Line Only Use character instead of line for table border dlowrey Word Tables 6 03-09-2015 11:29 AM
Delete Character on Single Line Only macro to add brackets to each line and add single quotes to each word in the line bracketandquotes Word VBA 17 02-16-2015 03:51 PM
first character NOT Alpha numeric or Tilde (~) delete. ksridh Word VBA 5 03-17-2014 12:45 AM
Change single character in PPT to another font macro rtwwpad PowerPoint 1 01-19-2013 05:08 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:21 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft