【マクロ・VBA】Replace関数を使用して会社名の表記を統一する

Sub 会社名を整備する()

Range("A1").Select
Selection.Offset(0, 1).EntireColumn.Insert

On Error Resume Next

Selection.EntireColumn.Copy Selection.Offset(0, 1).EntireColumn

Set myrange = Selection.Offset(0, 1).EntireColumn

bool = myrange.Replace("_JAPAN", "", LookAt:=xlPart)
bool = myrange.Replace("- JAPAN", "", LookAt:=xlPart)
bool = myrange.Replace("CORPORATION", "", LookAt:=xlPart)
bool = myrange.Replace("CORP", "", LookAt:=xlPart)
bool = myrange.Replace("KK", "", LookAt:=xlPart)
bool = myrange.Replace("K.K.", "", LookAt:=xlPart)
bool = myrange.Replace("CO.,LTD", "", LookAt:=xlPart)
bool = myrange.Replace("CO., LTD", "", LookAt:=xlPart)
bool = myrange.Replace("CO.,INC.", "", LookAt:=xlPart)
bool = myrange.Replace("LLC", "", LookAt:=xlPart)
bool = myrange.Replace("LTD", "", LookAt:=xlPart)
bool = myrange.Replace("INC", "", LookAt:=xlPart)

'スペースの削除
bool = myrange.Replace("  ", "", LookAt:=xlWhole)
bool = myrange.Replace(" ", "", LookAt:=xlWhole)

'記号の削除
bool = myrange.Replace("-", "", LookAt:=xlWhole)
bool = myrange.Replace("_", "", LookAt:=xlWhole)
bool = myrange.Replace(".", "", LookAt:=xlWhole)
bool = myrange.Replace("・", "", LookAt:=xlWhole)
bool = myrange.Replace(",", "", LookAt:=xlWhole)
bool = myrange.Replace("･", "", LookAt:=xlWhole)
bool = myrange.Replace("/", "", LookAt:=xlWhole)

'漢字の削除
bool = myrange.Replace("株式会社", "", LookAt:=xlWhole) 
bool = myrange.Replace("(株)", "", LookAt:=xlWhole)
bool = myrange.Replace("㈱", "", LookAt:=xlWhole)
bool = myrange.Replace("(有)", "", LookAt:=xlWhole)
bool = myrange.Replace("（有）", "", LookAt:=xlWhole)
bool = myrange.Replace("有限会社", "", LookAt:=xlWhole)
bool = myrange.Replace("㈲", "", LookAt:=xlWhole)
bool = myrange.Replace("(財)", "", LookAt:=xlWhole)
bool = myrange.Replace("(一財)", "", LookAt:=xlWhole)
bool = myrange.Replace("(公財)", "", LookAt:=xlWhole)
bool = myrange.Replace("一般財団法人", "", LookAt:=xlWhole)
bool = myrange.Replace("公益財団法人", "", LookAt:=xlWhole)
bool = myrange.Replace("財団法人", "", LookAt:=xlWhole)
bool = myrange.Replace("(資)", "", LookAt:=xlWhole)
bool = myrange.Replace("合資会社", "", LookAt:=xlWhole)
bool = myrange.Replace("合同会社", "", LookAt:=xlWhole)
bool = myrange.Replace("(同)", "", LookAt:=xlWhole)
bool = myrange.Replace("(合)", "", LookAt:=xlWhole)
bool = myrange.Replace("宗教法人", "", LookAt:=xlWhole)
bool = myrange.Replace("(宗)", "", LookAt:=xlWhole)
bool = myrange.Replace("一般社団法人", "", LookAt:=xlWhole)
bool = myrange.Replace("公益社団法人", "", LookAt:=xlWhole)
bool = myrange.Replace("社団法人", "", LookAt:=xlWhole)
bool = myrange.Replace("(社)", "", LookAt:=xlWhole)
bool = myrange.Replace("(一社)", "", LookAt:=xlWhole)
bool = myrange.Replace("(公社)", "", LookAt:=xlWhole)
bool = myrange.Replace("社会福祉法人", "", LookAt:=xlWhole)
bool = myrange.Replace("(社福)", "", LookAt:=xlWhole)
bool = myrange.Replace("独立行政法人", "", LookAt:=xlWhole)
bool = myrange.Replace("(独)", "", LookAt:=xlWhole)
bool = myrange.Replace("特定非営利活動法人", "", LookAt:=xlWhole)
bool = myrange.Replace("(特)", "", LookAt:=xlWhole)
bool = myrange.Replace("学校法人", "", LookAt:=xlWhole)
bool = myrange.Replace("(学)", "", LookAt:=xlWhole)
bool = myrange.Replace("医療法人", "", LookAt:=xlWhole)
bool = myrange.Replace("(医)", "", LookAt:=xlWhole)

MsgBox "完了"
End Sub