beginner VBA question, creating macro to clean range

[ivanwakeup]

Hi guys,

very new to VBA, and i'm really interested in learning it because I find it fascinating. I know there are plenty of ways to do what i'm trying to do, but i just want to understand what it looks like in VBA so I can start picking up the language.

I want to be able to:

clean all characters I define from a given range of cells. if i could do it based on the name of the range or even the length/values within the cells in that range, that would be great. heres what I have so far, to give you an idea of what I'm trying to do:

Sub removechars()


For Each cell In Selection


ActiveCell.Value = Substitute(ActiveCell.Value, "+", "")
ActiveCell.Value = Substitute(ActiveCell.Value, "(", "")
ActiveCell.Value = Substitute(ActiveCell.Value, ")", "")
ActiveCell.Value = Substitute(ActiveCell.Value, "-", "")
Next cell


End Sub


I have no clue if this is even remotely on track or if my syntax is correct. When I try to run this, I get a compiler error: sub or function not defined. Again, what i'd like this macro to do eventually is find the proper named column within my worksheet ("Phone", in this case) and clean that range accordingly. Can someone offer any help?

Thanks very much in advance

 

[Teeroy]

But without looping that will still only find the first column, my understanding from post #7 was that there could be more than 1 'phone' column in some sheets.

Thanks Jason, I missed that from the way it was written.

Don't think that can be done with ranges, I did provide code for that in post #2, but again, it is in a loop.

I moved the Range loop into an array loop, and only write to the worksheet once, to run the Regexp for a 10x speed improvement.

The following doesn't look quite as concise but is fast (I ran 2 columns of 10,000 rows of dummy data in 0.2 sec.);

Sub CleanPhoneNumbers()

Dim Rng As Range
Dim rTestHeadingRange As Range
Dim sFirstAddress As String
Dim a
Const sFindHeading As String = "Phone"

a = Timer()
Application.ScreenUpdating = False
On Error Resume Next
With ActiveSheet
    Set rTestHeadingRange = .Rows("1:4")
    'Find first "Phone"
    Set Rng = rTestHeadingRange.Find(what:=sFindHeading, LookIn:=xlValues, lookat:=xlPart)
    'Check if match exists, if not exit sub
    If Rng Is Nothing Then
        MsgBox "Heading Not Found"
        Application.ScreenUpdating = True
        Exit Sub
    End If
    'Run removechars2 and check for other matches
    sFirstAddress = Rng.Address
    Do
        removechars2 Range(Rng.Offset(1), Application.Intersect(Rng.EntireColumn, .UsedRange.Rows(.UsedRange.Rows.Count)))
        Set Rng = rTestHeadingRange.FindNext(Rng)
    Loop While Not Rng Is Nothing And Rng.Address <> sFirstAddress

End With
Application.ScreenUpdating = True
'MsgBox Timer() - a & " seconds" 'uncomment this if you want to get a time check for processing.
End Sub

Sub removechars2(Rng As Range)
Dim Temp
Dim i As Long
Temp = Application.Transpose(Rng)
With CreateObject("vbscript.regexp")
    For i = 1 To UBound(Temp)
        .Pattern = "[^0-9]"
        .Global = True
        Temp(i) = .Replace(Temp(i), "")
        Temp(i) = Right(Temp(i), 9)
    Next
End With
Rng.Value = Application.Transpose(Temp)
End Sub

Teeroy

 

[jasonb75]

I would never have thought of transposing the range to an array to process the data faster, that is a significant improvement over my methods.

 

[Teeroy]

I would never have thought of transposing the range to an array to process the data faster, that is a significant improvement over my methods.

I think I orginially picked up the idea here from Rick Rothstein but Chip Pearson's web site has some really good information about how to use it.

 

[dcardno]

Teeroy, can you suggest good sources to learn about vbscript and regexp? I have never seen either, but they are obviously very powerful tools.

 

[Teeroy]

dcardno,

I use Regular Expression Tutorial to get regex pattern info as I need it (I don't use it often enough to remember it ).

For VBScript I have a couple of books but I don't actually use it much. Have a look at Tutorialpoint - VBScript; I just googled and this came up and it looked OK (for a quick glance). VBScript is a similar language to VB / VBA but the subtle differences can be frustrating. The only 2 things I think I use in VBA from VBScript are Regex and the Scripting Dictionary object.

 

[dcardno]

Thanks Teeroy - I'll check those out.