Can these codes be summarized?
A to Z is chr(65) to chr(90)
so you could shorten it this way:
dim i as long
for i = asc("A") to asc("Z")
with selection
.replace what:=chr(i), ....
end with
next i
You could set up a loop for the other 3:
dim myList as variant
dim ictr as long
mylist = array("-","/","\")
for ictr = lbound(mylist) to ubound(mylist)
with selection
.replace what:=mylist(ictr).....
end with
next ictr
Edmund Seet wrote:
This macro is to remove character {A~Z, "-" , "/" , "\"}
from the string within my selected range of cells.
I'm a VBA rookie.
Can these codes be summarized?
Sub Strictly_Numbers()
With Selection
.Replace What:="-", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="/", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="\", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="A", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="B", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="C", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="D", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="E", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="F", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="G", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="H", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="I", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="J", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="K", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="L", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="M", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="N", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="O", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="P", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="Q", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="R", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="S", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="T", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="U", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="V", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="W", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="X", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="Y", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="Z", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub
TIA
Edmund Seet
--
Dave Peterson
|