CODEWITHSUNDEEP
excelvba
Akshay Sachdev
Akshay Sachdev

Reputation: 9

Replacing Special Characters in Excel VBA and adding columns

I am really new to Excel VBA Macros, as my job demanded something challenging. To give you all a brief intro, I receive a file on a daily basis where I have to find all special characters ( @ * ( ) _ + [ ] \ : ; “ ‘ , . / ? ) from column A. Add three columns C, D and E which should auto-fill values viz - Column C should fill in "XYZ", Column D to have "ABC" and Column E to have "NA". The same values should be copied in the new columns that get added till the last data row.

I did create something, which works for removing special characters, except the double quotes ("). Also, I did not find a way to have the file saved as an EXE, that when clicked finds the Excel Workbook open with its name and executes this function.

I would really appreciate if someone can help me with this code. I'm really naive with VBA programming. If this works out, would be a big help for my day to day work management!

Appreciate the help.

Thanks, Akshay

 Sub Remove_Special()
Columns("A").Replace _
 What:=";", Replacement:=""
Columns("A").Replace _
 What:=":", Replacement:=""
Columns("A").Replace _
 What:=",", Replacement:=""
Columns("A").Replace _
 What:="(", Replacement:=""
Columns("A").Replace _
 What:=")", Replacement:=""
Columns("A").Replace _
 What:="{", Replacement:=""
Columns("A").Replace _
 What:="}", Replacement:=""
Columns("A").Replace _
 What:="[", Replacement:=""
Columns("A").Replace _
 What:="]", Replacement:=""
Columns("A").Replace _
 What:="~+", Replacement:=""
Columns("A").Replace _
 What:="~*", Replacement:=""
Columns("A").Replace _
 What:="~?", Replacement:=""
Columns("A").Replace _
 What:="_", Replacement:=""
Columns("A").Replace _
 What:=".", Replacement:=""
Columns("A").Replace _
 What:="'", Replacement:=""
Columns("A").Replace _
 What:="\", Replacement:=""
Columns("A").Replace _
 What:="/", Replacement:=""
Columns("A").Replace _
 What:=".", Replacement:=""
Columns("A").Replace _
 What:="@", Replacement:=""
Columns("A").Replace _
 What:=Chr(34), Replacement:=""

MsgBox "All special characters have been removed", vbOKOnly
End Sub

Upvotes: 0

Views: 2963

Answers (1)

Pierre Delecto
Pierre Delecto

Reputation: 451

Take a look here for something similar: VBA - If a cell in column A is not blank the column B equals

Also try this for removing the double quotes:

myVariable = Replace(myVariable, Chr(34), "")

You could just copy and paste the workbook's contents to the macro-enabled workbook instead of creating an executable file.

Upvotes: 0

Related Questions