Remove letters from range

[Villalobos]

Hello,

I would like to ask that what is the fastest way to remove all letters from a range in one shot?
In range G9:G the numbers ended with base units, like PC, KG or L and etc. Could you help me to figure out a code which can remove all letters (English Alphabet) from this range?

This is the part of my code:

.Range("G9:G" & Lastrow).Replace What:="PC", Replacement:=""
.Range("G9:G" & LastRow).Replace What:="KG", Replacement:=""
.Range("G9:G" & LastRow).Replace What:="L", Replacement:=""
&

Thank you in advance!

 

[Asheesh]

Hi,

The below link should help...

http://www.thespreadsheetguru.com/the-code-vault/2014/4/14/find-and-replace-all

 

[Stevie]

I had a play:

Sub remLet()
    Dim i As Integer, j As Integer
    Dim str As String
    For i = 1 To 20
        str = Sheets("Sheet1").Cells(i, 1).Text
        For j = 1 To Len(str)
            Select Case Asc(Mid(str, j, 1))
                Case 65 To 89, 97 To 122
                    str = Replace(str, Mid(str, j, 1), "Z")
            End Select
        Next
        Sheets("Sheet1").Cells(i, 1) = Replace(str, "Z", "")
    Next
End Sub

For each cell in A1:A20 on Sheet1, I replace each letter with the letter Z, then when I am done with that cell, I replace all Zs for an empty string "".
Seems to work fine, modify ranges/turn into function for your own purposes if required.

 

[shrivallabha]

You can use Regular Expressions also like below.

Public Sub RemoveText()
Dim regEx As Object: Set regEx = CreateObject("VBScript.RegExp")
With regEx
    .Global = True
    .IgnoreCase = True
    .Pattern = "[A-Z]"
End With

For Each Rng In Range("G9:G" & Range("G" & Rows.Count).End(xlUp).Row)
    Rng.Value = regEx.Replace(Rng.Value, "")
Next Rng
End Sub

 

[Villalobos]

Thank you Guys!