Remove 60+ types of characters/text-strings/numbers

Xiq · Nov 20, 2013

Hi great minds on the Chandoo!

I made a macro (copy pasting some things together) that removes a series of characters/text-strings/numbers, but I'm very very green in the arts of VBA and it seems to be kinda inefficient. I would greatly appreciate some (easy to understand) feedback or solution to my problem.

Please reference to my code below for the characters/text-strings/numbers I want to remove.

Many thanks!
Xiq

PHP:

Sub RemoveStuff()
' Applies a "Remove" function on the selected cell or cells
' CTRL + SHIFT + D
    Dim ActSheet As Worksheet
    Dim SelRange As Range, cell As Range

    Set ActSheet = ActiveSheet
    Set SelRange = Selection
    ActSheet.Select
    SelRange.Select

    For Each cell In SelRange
        ' Number Series "00-13"
        cell = WorksheetFunction.Substitute(cell, "00", "")
        cell = WorksheetFunction.Substitute(cell, "01", "")
        cell = WorksheetFunction.Substitute(cell, "02", "")
        cell = WorksheetFunction.Substitute(cell, "03", "")
        cell = WorksheetFunction.Substitute(cell, "04", "")
        cell = WorksheetFunction.Substitute(cell, "05", "")
        cell = WorksheetFunction.Substitute(cell, "06", "")
        cell = WorksheetFunction.Substitute(cell, "07", "")
        cell = WorksheetFunction.Substitute(cell, "08", "")
        cell = WorksheetFunction.Substitute(cell, "09", "")
        cell = WorksheetFunction.Substitute(cell, "10", "")
        cell = WorksheetFunction.Substitute(cell, "11", "")
        cell = WorksheetFunction.Substitute(cell, "12", "")
        cell = WorksheetFunction.Substitute(cell, "13", "")
       
        ' Number Series "2000-2013"
        cell = WorksheetFunction.Substitute(cell, "2000", "")
        cell = WorksheetFunction.Substitute(cell, "2001", "")
        cell = WorksheetFunction.Substitute(cell, "2002", "")
        cell = WorksheetFunction.Substitute(cell, "2003", "")
        cell = WorksheetFunction.Substitute(cell, "2004", "")
        cell = WorksheetFunction.Substitute(cell, "2005", "")
        cell = WorksheetFunction.Substitute(cell, "2006", "")
        cell = WorksheetFunction.Substitute(cell, "2007", "")
        cell = WorksheetFunction.Substitute(cell, "2008", "")
        cell = WorksheetFunction.Substitute(cell, "2009", "")
        cell = WorksheetFunction.Substitute(cell, "2010", "")
        cell = WorksheetFunction.Substitute(cell, "2011", "")
        cell = WorksheetFunction.Substitute(cell, "2012", "")
        cell = WorksheetFunction.Substitute(cell, "2013", "")
       
        ' Letter (lowerCase only) Series "a-z"
        cell = WorksheetFunction.Substitute(cell, "a", "")
        cell = WorksheetFunction.Substitute(cell, "b", "")
        cell = WorksheetFunction.Substitute(cell, "c", "")
        cell = WorksheetFunction.Substitute(cell, "d", "")
        cell = WorksheetFunction.Substitute(cell, "e", "")
        cell = WorksheetFunction.Substitute(cell, "f", "")
        cell = WorksheetFunction.Substitute(cell, "g", "")
        cell = WorksheetFunction.Substitute(cell, "h", "")
        cell = WorksheetFunction.Substitute(cell, "i", "")
        cell = WorksheetFunction.Substitute(cell, "j", "")
        cell = WorksheetFunction.Substitute(cell, "k", "")
        cell = WorksheetFunction.Substitute(cell, "l", "")
        cell = WorksheetFunction.Substitute(cell, "m", "")
        cell = WorksheetFunction.Substitute(cell, "n", "")
        cell = WorksheetFunction.Substitute(cell, "o", "")
        cell = WorksheetFunction.Substitute(cell, "p", "")
        cell = WorksheetFunction.Substitute(cell, "q", "")
        cell = WorksheetFunction.Substitute(cell, "r", "")
        cell = WorksheetFunction.Substitute(cell, "s", "")
        cell = WorksheetFunction.Substitute(cell, "t", "")
        cell = WorksheetFunction.Substitute(cell, "u", "")
        cell = WorksheetFunction.Substitute(cell, "v", "")
        cell = WorksheetFunction.Substitute(cell, "w", "")
        cell = WorksheetFunction.Substitute(cell, "x", "")
        cell = WorksheetFunction.Substitute(cell, "y", "")
        cell = WorksheetFunction.Substitute(cell, "z", "")
       
        ' Character Series char(0032), char(0160) and "-/[]&(),'`."
        cell = WorksheetFunction.Substitute(cell, "-", "")
        cell = WorksheetFunction.Substitute(cell, "/", "")
        cell = WorksheetFunction.Substitute(cell, "[", "")
        cell = WorksheetFunction.Substitute(cell, "]", "")
        cell = WorksheetFunction.Substitute(cell, "&", "")
        cell = WorksheetFunction.Substitute(cell, "(", "")
        cell = WorksheetFunction.Substitute(cell, ")", "")
        cell = WorksheetFunction.Substitute(cell, ",", "")
        cell = WorksheetFunction.Substitute(cell, "'", "")
        cell = WorksheetFunction.Substitute(cell, "`", "")
        cell = WorksheetFunction.Substitute(cell, ".", "")
        cell = WorksheetFunction.Substitute(cell, "  ", "")
        cell = WorksheetFunction.Substitute(cell, " ", "")

    Next
End Sub

SirJB7 · Nov 20, 2013

Hi, Xiq!
Try replacing all those WorksheetFunction.Substitute by the Range.Replace method.
Regards!
PS: It works too for f.....g Char(160)

EDITED

PS2: Replaced VBA function Replace (which doesn't exist) by the Range.Replace method (which exists!, and it's the used one!)... I mixed VBA function StrRplace and Range.Replace method...

Xiq · Nov 20, 2013

No guillotine I hope?

SirJB7 · Nov 20, 2013

Hi, Xiq!

Not this time...

Try this code:

Code:

Option Explicit

Sub XiQ_160_161_162__256()
    ' Applies a "Remove" function on the selected cell or cells
' CTRL + SHIFT + D
    '
    ' constants
    Const ks1st = "00 01 02 03 04 05 06 07 08 09 10 11 12 13"
    Const ks2nd = "2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013"
    Const ks3rd = "a b c d e f g h i j k l m n o p q r s t u v w x y z"
    Const ks4th = "- / [ ] & ( ) , ' ` ."
    ' declarations
    Dim vArr0 As Variant, vArr1 As Variant, vArr2 As Variant, vArr3 As Variant, vArr4 As Variant
    Dim rng As Range, cell As Range
    Dim I As Integer
    ' start
    vArr0 = Split(" X" & Chr(160), "X")
    vArr1 = Split(ks1st)
    vArr2 = Split(ks2nd)
    vArr3 = Split(ks3rd)
    vArr4 = Split(ks4th)
    Set rng = Selection
    ' process
    For Each cell In rng
        With cell
            For I = 0 To UBound(vArr0)
                .Value = Replace(.Value, vArr0(I), "")
            Next I
            For I = 0 To UBound(vArr1)
                .Value = Replace(.Value, vArr1(I), "")
            Next I
            For I = 0 To UBound(vArr2)
                .Value = Replace(.Value, vArr2(I), "")
            Next I
            For I = 0 To UBound(vArr3)
                .Value = Replace(.Value, vArr3(I), "")
            Next I
            For I = 0 To UBound(vArr4)
                .Value = Replace(.Value, vArr4(I), "")
            Next I
        End With
    Next cell
    ' end
    Set cell = Nothing
    Set rng = Nothing
    Beep
End Sub

Regards!

PS: ... just a rusty shaw.

EDITED

PS2: Removed "Activesheet" from Selection statement

SirJB7 · Nov 20, 2013

Hi, Xiq!
But you have a problem if you run the replacements in that order, either with your version or mine.
If you 1st replace 01-13 by "" then you won't find any 2000-2013 and will keep lots of 20.
Regards!

Xiq · Nov 20, 2013

*Xiq looks at "Sub XiQ_160_161_162__256()" in the code and is confused*

Thanks SirJB!
The macro got stuck at "Set rng = ActiveSheet.Selection" though.

PS: What happened to my post

Xiq · Nov 20, 2013

SirJB7 said:
If you 1st replace 01-13 by "" then you won't find any 2000-2013 and will keep lots of 20.
Regards!

Good point, so maybe turn it around or leave the "2000-2013" and simply add "20"?

PS: I'll be back tomorrow

SirJB7 · Nov 20, 2013

Hi, Xiq!

Actually this will avoid the loop for each cell and do the changes in the proper order:

Code:

Option Explicit

Sub XiQ_160_161_162__256()
    ' Applies a "Remove" function on the selected cell or cells
' CTRL + SHIFT + D
    '
    ' constants
    Const ks1st = "2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013"
    Const ks2nd = "00 01 02 03 04 05 06 07 08 09 10 11 12 13"
    Const ks3rd = "a b c d e f g h i j k l m n o p q r s t u v w x y z"
    Const ks4th = "- / [ ] & ( ) , ' ` ."
    ' declarations
    Dim vArr0 As Variant, vArr1 As Variant, vArr2 As Variant, vArr3 As Variant, vArr4 As Variant
    Dim rng As Range
    Dim I As Integer
    ' start
    vArr0 = Split(" X" & Chr(160), "X")
    vArr1 = Split(ks1st)
    vArr2 = Split(ks2nd)
    vArr3 = Split(ks3rd)
    vArr4 = Split(ks4th)
    Set rng = Selection
    ' process
    With rng
        For I = 0 To UBound(vArr1)
            .Replace What:=vArr1(I), Replacement:="", LookAt:=xlPart, MatchCase:=True
        Next I
        For I = 0 To UBound(vArr2)
            .Replace What:=vArr2(I), Replacement:="", LookAt:=xlPart, MatchCase:=True
        Next I
        For I = 0 To UBound(vArr3)
            .Replace What:=vArr3(I), Replacement:="", LookAt:=xlPart, MatchCase:=True
        Next I
        For I = 0 To UBound(vArr4)
            .Replace What:=vArr4(I), Replacement:="", LookAt:=xlPart, MatchCase:=True
        Next I
        For I = 0 To UBound(vArr0)
            .Replace What:=vArr0(I), Replacement:="", LookAt:=xlPart, MatchCase:=True
        Next I
    End With
    ' end
    Set rng = Nothing
    Beep
End Sub

Regards!

SirJB7 · Nov 20, 2013

Xiq said:
The macro got stuck at "Set rng = ActiveSheet.Selection" though.

Hi, Xiq!
Check my above edited post

Regards!

Xiq · Nov 21, 2013

Thanks a lot SirJB7!!

It goes beyond my VBA knowledge, but at least I was able to remove the evil BEEP

Question though: How come my code can handle text via a reference, but yours can't?

SirJB7 · Nov 21, 2013

Hi, Xiq!
I don't understand you, maybe in plain English for foreigners?

Regards!
PS: The Beep is just to say "it's over", sometimes if the code doesn't change selections or display updated ranges, one doesn't realize when the process has ended, the blinking cursor of the VBA editor may lead to confusion.

SirJB7 · Nov 21, 2013

Hi, Xiq!

Back to the code, it isn't so advanced as to not be understandable, let us go thru it:

a) Constants
A string separated by a non-embedded character (space for all but the last, which it's a space, so the separator is an "X", but could be any other)

b) Arrays
Dimensioned as variants without indicating the dimension (Split will take care)

c) Fill the arrays
Split function takes a string and distribute each chunk into an array position, using as separator the character indicated in the 2nd argument (if omitted, then space)

d) Changing values
With the rng defined as the selected area, the For...Next loop (from 0 to upper bound of the array, i.e. no. olf elements) uses the Range.Replace method for changing each char defined in the array. Repeated for all arrays.

e) The final end
Beep!

Regards!

Xiq · Nov 22, 2013

@SirJB7

SirJB7 · Nov 22, 2013

Hi, Xiq!
Don't be shy and ask anything you don't understand. BTW, I still have a doubt:

Xiq said:
Question though: How come my code can handle text via a reference, but yours can't?

SirJB7 said:
I don't understand you, maybe in plain English for foreigners?

Regards!

Xiq · Nov 29, 2013

Hi SirJB7,

I haven't forgotten your question(s)

Will come back when I know what to ask.

PS: plain English isn't my strong point as a foreigner

SirJB7 · Nov 30, 2013

Xiq said:
Question though: How come my code can handle text via a reference, but yours can't?

Hi, Xiq!
This is what I (me, not you) don't understand. What did you mean?
Regards!

Remove 60+ types of characters/text-strings/numbers

Xiq

Active Member

SirJB7

Excel Rōnin

Xiq

Active Member

SirJB7

Excel Rōnin

SirJB7

Excel Rōnin

Xiq

Active Member

Xiq

Active Member

SirJB7

Excel Rōnin

SirJB7

Excel Rōnin

Xiq

Active Member

SirJB7

Excel Rōnin

SirJB7

Excel Rōnin

Xiq

Active Member

SirJB7

Excel Rōnin

Xiq

Active Member

SirJB7

Excel Rōnin