View Full Version : Macro for removing 2 letters word

Hao Xu
03-24-2017, 07:57 AM
Hi guys!:

I need really need your help for this.
I was looking for a macro that could automatically detect and remove 2 letter words (for example: or, if, hg, wa, rt, op...) in a selected range (it will be almost always in column A)

Also, i wanted to put this macro some words that it should not remove (because i work with some products with 2 letter brand such as LG, CH, DG...) I was thinking that i could add a list to this macro.
For example

in and out we went to the top of the hill---------------------> and out went the top hill
of one to many we see an answer------------------------------>one many see answer
df is gh is fe on off----------------------->is is on off ("is" is not removed because i need this word, as well as a list that i have of 2 letters word)

Thank you in advance guys!

Waiting for your replies.

03-24-2017, 08:28 AM
Option Compare Text
Option Explicit

Sub Test()
Dim Words, W, X
Dim i As Long
Dim Reserved As Range, cel As Range
Dim txt As String

Set Reserved = [C1].CurrentRegion 'Set to required range

For Each cel In Selection
Words = Split(cel, " ")
For i = 0 To UBound(Words)
W = Words(i)
If Len(W) = 2 Then
Set X = Reserved.Find(W)
If X Is Nothing Then Words(i) = ""
End If

txt = Join(Words, " ")
For i = 2 To UBound(Words)
txt = Replace(txt, String(i, " "), " ")
Next i
cel = Trim(txt)
Next cel
End Sub

Hao Xu
03-24-2017, 08:37 AM
Hi Mark:

Thank you for your reply, but this macro is giving me an error of compilation, and it highlights here:

cel = Trim(txt)

(I'm a complete newbie of vba, so i truly thank you for trying to help me)

03-24-2017, 08:42 AM
Can you Post a workbook (http://www.vbaexpress.com/forum/faq.php?s=&do=search&q=attachment&titleandtext=1&match=all) with sample data?

03-24-2017, 08:58 AM
I'd use

Sub M_snb()
End Sub

Hao Xu
03-24-2017, 09:02 AM

Please find the sample here:

03-24-2017, 09:15 AM
Seems OK. Reserved list added to sheet.

Hao Xu
03-24-2017, 09:23 AM
That is what i was looking for!!!!!!

How do i get the code of that macro?

Sorry i'm a complete newbie

03-24-2017, 09:27 AM
The code is as Post #2 and appears in Module 1 of the VBA Project18758

03-26-2017, 10:14 PM
Re: Sorry i'm a complete newbie
Even newbies should post the hyperlinks to the other forums where you posted.