|
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 |
Can these codes be summarized?
I'm sure there's an easier way...
Sub StrictlyNumbers() dim i as long i=45:MyReplace(i) ' '-' i=47:MyReplace(i) ' '/' i=92:MyReplace(i) ' '\' for i=65 to 90 ' A - Z MyReplace(i) next for i= 97 to 122 ' a - z MyReplace(i) next End Sub Sub MyReplace(code as string) Selection.Replace What:=chr(code), _ Replacement:="", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False End Sub With any code, look for repetitive lines- they're the ones that you can often put into subs. HTH Patrick Molloy Microsoft Excel MVP -----Original Message----- 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 . |
| All times are GMT +1. The time now is 06:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com