ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Can these codes be summarized? (https://www.excelbanter.com/excel-programming/271158-can-these-codes-summarized.html)

Edmund Seet
Can these codes be summarized?
 
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[_3_]
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


All times are GMT +1. The time now is 07:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com