Delete other values except "A" and "B" and "C"

**HerryMarkowitz** · 01-10-2014, 07:45 AM

Greetings,
I have following code in the enclosed file.

Sub Macro1()
Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Replace What:=">", Replacement:="", lookat:=xlPart
Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Replace What:="BT", Replacement:="", lookat:=xlPart
Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Replace What:="p", Replacement:="", lookat:=xlPart
Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Replace What:="t", Replacement:="", lookat:=xlPart
Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Replace What:=" ", Replacement:="", lookat:=xlPart
Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Replace What:="  ", Replacement:="", lookat:=xlPart
End Sub

Open enclosed file and run Macro1 in order to understand what Macro1 is doing...

I need a macro which will do same operation of Macro1 but
delete other values except "A" and "B" and "C"
or
replace other values except "A" and "B" and "C" to nothing.

So, my aim is to shorten a Macro1.
Best wishes.

**ragulduy** · 01-10-2014, 08:01 AM

I reckon how you have it might be the quickest, the only way I could think of replacing everything except certain characters would be to loop through each character in the string and check it individually, or to use a formula to break the string into an array of characters and use a vba function to concatenate al the A's. B's and C's.

That said, if you are just interested in the length of your code as a character count:

Sub Macro1()
With Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
   .replace ">","",xlPart
   .replace "B","",xlPart
   .replace "p","",xlPart
   .replace "t","",xlPart
   .replace " ","",xlPart
End With
End Sub

is perhaps the same?

**HerryMarkowitz** · 01-10-2014, 08:14 AM

Hi yudlugar,
Thanks for the answer.
I need this code because cell characters could be other characters not only ">", "B", "p", "t".
So, I need a macro which will delete everything other than A, B and C.
Any other advice will be thankful...

**ragulduy** · 01-10-2014, 08:19 AM

Function replace_but_ABC(str As String)
Dim i
For i = 1 To Len(str)
    If Mid(str, i, 1) = "A" Or Mid(str, i, 1) = "B" Or Mid(str, i, 1) = "C" Then
        replace_but_ABC = replace_but_ABC & Mid(str, i, 1)
    End If
Next i
End Function

**HerryMarkowitz** · 01-10-2014, 08:38 AM

I prefer regular code instead of function. If possible ?

**ragulduy** · 01-11-2014, 04:39 AM

Sub replace_but_ABC()
Dim i, str, replace_but_ABC
str = "AAasdvjVBC"
For i = 1 To Len(str)
    If Mid(str, i, 1) = "A" Or Mid(str, i, 1) = "B" Or Mid(str, i, 1) = "C" Then
        replace_but_ABC = replace_but_ABC & Mid(str, i, 1)
    End If
Next i
msgbox replace_but_ABC
End Sub

**HerryMarkowitz** · 01-11-2014, 05:04 AM

Not solved.
But thanks anyway.

Delete other values except "A" and "B" and "C"

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Delete other values except "A" and "B" and "C"

Re: Delete other values except "A" and "B" and "C"

Re: Delete other values except "A" and "B" and "C"

Re: Delete other values except "A" and "B" and "C"

Re: Delete other values except "A" and "B" and "C"

Re: Delete other values except "A" and "B" and "C"

Re: Delete other values except "A" and "B" and "C"

Thread Information

Users Browsing this Thread

Similar Threads

Don't know the Forumlas with problem either using "vlookup" "if" "and" or "index" "match"

[SOLVED] How to USE """"" cells count """"" change font color

Seperating "colour" and "size" values into seperate columns from the "product name"

Excel 2007 : Match Values and Return "Present" "Absent" "Per Diem" or remain blank

Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next

Bookmarks

Bookmarks

Posting Permissions