Delete Character on Single Line Only

Tye30 · #1 04-17-2017, 01:48 PM

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

macropod · #2 04-17-2017, 05:36 PM

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.

Tye30 · #3 04-18-2017, 11:17 AM

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.

macropod · #4 04-18-2017, 03:58 PM

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.

Tye30 · #5 04-19-2017, 06:40 AM

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.

macropod · #6 04-19-2017, 12:27 PM

Quote:

Originally Posted by Tye30

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

Tye30 · #7 04-20-2017, 09:49 AM

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

macropod · #8 04-20-2017, 04:56 PM

Quote:

Originally Posted by Tye30

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Every page indents first line one character	glennhardy	Word	6	02-05-2016 04:05 PM
Use character instead of line for table border	dlowrey	Word Tables	6	03-09-2015 11:29 AM
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