none
amending a macro RRS feed

  • Question

  • hi friends

    i'm not a VBA developer.

    some months ago, an scripting guy helped me with a macro to remove paragraph marks.

    now i need to add some lines of code to it which finds any one or more white spaces and replace them with a single space. so using record macro feature i recorded a macro which finds any one or more white spaces and replace them with a single space and the code is revealed. now i need to add this code to my previous macro.

    now may someone please amend these line of codes to a more standard macro ? ( i can't do this myself since a''m not a developer)

    i think it can be shortened and more correct.

    Sub RemoveParagraphMarks()
        With Selection.Find
            .ClearFormatting
            .Text = "^p"
            .Replacement.ClearFormatting
            .Replacement.Text = " "
            .MatchWildcards = False
            .MatchWholeWord = False
            .Wrap = wdFindStop
            .Execute Replace:=wdReplaceAll
            
        End With
        
        With Selection.Find
            .Text = "^w"
            .Replacement.Text = " "
            .Forward = True
            .Wrap = wdFindAsk
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchKashida = False
            .MatchDiacritics = False
            .MatchAlefHamza = False
            .MatchControl = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With
        Selection.Find.Execute Replace:=wdReplaceAll
    
    End Sub
    

    Wednesday, April 1, 2015 6:20 AM

Answers

  • Hi,

    You macro looks good. The first part will do the search and replace for the paragraph marks. The second bit will remove the spaces. Every line that has False set to that part can be removed. At the end place the selection.find.execute replace: wdReplaceAll in the with statement. So place that line beforen the last End With.

    Shortens your code and does exactly what it should do.

    Maurice


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. Thank You

    • Marked as answer by john.s2011 Wednesday, April 1, 2015 9:59 PM
    Wednesday, April 1, 2015 8:11 AM
  • Hi John,

    You need to add one line of code. I've added it below so you can copy the code. What it does is surpess the question to search from the beginning of the document.

    Sub RemoveParagraphMarks()
        Application.DisplayAlerts = wdAlertsNone
        With Selection.Find
            .ClearFormatting
            .Text = "^p"
            .Replacement.ClearFormatting
            .Replacement.Text = " "
            .Wrap = wdFindStop
            .Execute Replace:=wdReplaceAll
        End With
        With Selection.Find
            .Text = "^w"
            .Replacement.Text = " "
            .Forward = True
            .Wrap = wdFindAsk
        Selection.Find.Execute Replace:=wdReplaceAll
        End With
    End Sub
    
     

    As you can see I added the "Application.DisplayAlerts = wdAlertsNone" line of code before the macro actually starts replacing.

    Maurice


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. Thank You

    • Marked as answer by john.s2011 Friday, April 3, 2015 6:01 PM
    Friday, April 3, 2015 2:07 PM

All replies

  • Hi,

    You macro looks good. The first part will do the search and replace for the paragraph marks. The second bit will remove the spaces. Every line that has False set to that part can be removed. At the end place the selection.find.execute replace: wdReplaceAll in the with statement. So place that line beforen the last End With.

    Shortens your code and does exactly what it should do.

    Maurice


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. Thank You

    • Marked as answer by john.s2011 Wednesday, April 1, 2015 9:59 PM
    Wednesday, April 1, 2015 8:11 AM
  • Hi,

    You macro looks good. The first part will do the search and replace for the paragraph marks. The second bit will remove the spaces. Every line that has False set to that part can be removed. At the end place the selection.find.execute replace: wdReplaceAll in the with statement. So place that line beforen the last End With.

    Shortens your code and does exactly what it should do.

    Maurice


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. Thank You

    Hi Maurice

    thanks for your guide.

    but a simple problem: after running this macro, system asks me this:

    we made 20 replacements. do you want to continue searching from the beginning ?

    what code i must add & where in my macro , so that system doesn't ask me this question & set it to No (since i need it be run only on my selection area) 

    Wednesday, April 1, 2015 10:02 PM
  • Hi John,

    You need to add one line of code. I've added it below so you can copy the code. What it does is surpess the question to search from the beginning of the document.

    Sub RemoveParagraphMarks()
        Application.DisplayAlerts = wdAlertsNone
        With Selection.Find
            .ClearFormatting
            .Text = "^p"
            .Replacement.ClearFormatting
            .Replacement.Text = " "
            .Wrap = wdFindStop
            .Execute Replace:=wdReplaceAll
        End With
        With Selection.Find
            .Text = "^w"
            .Replacement.Text = " "
            .Forward = True
            .Wrap = wdFindAsk
        Selection.Find.Execute Replace:=wdReplaceAll
        End With
    End Sub
    
     

    As you can see I added the "Application.DisplayAlerts = wdAlertsNone" line of code before the macro actually starts replacing.

    Maurice


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. Thank You

    • Marked as answer by john.s2011 Friday, April 3, 2015 6:01 PM
    Friday, April 3, 2015 2:07 PM
  • Hi John,

    You need to add one line of code. I've added it below so you can copy the code. What it does is surpess the question to search from the beginning of the document.

    Sub RemoveParagraphMarks()
        Application.DisplayAlerts = wdAlertsNone
        With Selection.Find
            .ClearFormatting
            .Text = "^p"
            .Replacement.ClearFormatting
            .Replacement.Text = " "
            .Wrap = wdFindStop
            .Execute Replace:=wdReplaceAll
        End With
        With Selection.Find
            .Text = "^w"
            .Replacement.Text = " "
            .Forward = True
            .Wrap = wdFindAsk
        Selection.Find.Execute Replace:=wdReplaceAll
        End With
    End Sub
    
     

    As you can see I added the "Application.DisplayAlerts = wdAlertsNone" line of code before the macro actually starts replacing.

    Maurice


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. Thank You

    Hi Maurice

    that's Great, worked as expected.

    special thanks. VBA is really valuable, i wish i be able to learn it .

    best regards

    Friday, April 3, 2015 6:02 PM