VBA and special characters

[Ezguy4u]

So my problem is I want to delete those special characters, ▼ ▼ ▼ ▼ ▼ ▼, using a VBA program. I tried using record but as you can see VBA turned those characters into ????. I have tried coping and pasting into a VBA window and no joy. Now I am going to have several lines like in cell A1 but I am showing just one line. And the words after the special characters will change. So the input is cell A1 and the output is cell A5. Any questions, comments or suggestions let me know and thanks for your time.
Arnold

Sub DeleteSpecial()

    Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "? ? ? ? ? ?"
    Range("A5").Select
    Cells.Replace What:="? ? ? ? ? ?", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

24-05-06 my question.xlsm
	A
1	▼ ▼ ▼ ▼ ▼ ▼303 Alaska Air Group ALK 45.53 ▲1 3 5 1.55 55- 80 (20- 75%) 10.3 NIL 4.40 NIL 2 3/31◆d.92 d.62 3/31 NIL NIL YES
2
3
4
5	303 Alaska Air Group ALK 45.53 ▲1 3 5 1.55 55- 80 (20- 75%) 10.3 NIL 4.40 NIL 2 3/31◆d.92 d.62 3/31 NIL NIL YES
Data1

 

[MARK858]

Try on a test page

Sub DeleteSpecial()
   Cells.Replace What:=ChrW(9660), Replacement:="", LookAt:=xlPart
End Sub

to see if it picks up the character

 

[Cubist]

Maybe this with formula.

=SUBSTITUTE(A1,"▼","")

Edit: @MARK858
This picked up on my end with formula.

=SUBSTITUTE(A1,UNICHAR(9660),"")

 

[MARK858]

Edit: @MARK858
This picked up on my end with formula.

Yes tested it at my end beforehand with formula but wanted the OP to test the VBA to make sure they have the same character set

 

[Akuini]

VBA Code:

Sub DeleteSpecial()
   Cells.Replace What:=ChrW(9660), Replacement:="", LookAt:=xlPart
End Sub

I suggest to provide a method to get the number "9660". Maybe this:

Book1
	A	B
1	▼	9660
2	▲	9650
3	◆	9670
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=UNICODE(A1)

then:

Sub Delete_Special_Char()
  ActiveSheet.Cells.Replace What:=ChrW(9660), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  ActiveSheet.Cells.Replace What:=ChrW(9650), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  ActiveSheet.Cells.Replace What:=ChrW(9670), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 
End Sub

 

[MARK858]

I suggest to provide a method to get the number "9660". Maybe this:

With the OP's original data it would be

=UNICODE(LEFT(A1,1))

The OP's question only requests the ▼ removed

 

[Akuini]

The OP's question only requests the ▼ removed

Ah, you're right, I thought the OP wants to remove all special characters.

 

[Ezguy4u]

Ok Thank you guys for your help. Cubist your suggestion is very helpful but I need it work in a VBA program. Mark858 and Akuini you folks hit the nail right on the head. And while I didn't ask to remove the other special characters, I will be removing other special characters now. All I needed was a little push in the right direction. I will be using unicode for the other special characters. Again thank you all for your time.

 

[MARK858]

You're welcome, happy we could help