beginner VBA question, creating macro to clean range

ivanwakeup

New Member
Joined
Aug 22, 2014
Messages
12
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 :)
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
You syntax might not be correct, but at least it makes sense :)

Code:
Sub removechars()
Dim cell As Range
For Each cell In Selection
    cell.Value = Application.WorksheetFunction.Substitute(ActiveCell.Value, "+", "")
    ' etc
Next cell
End Sub

Although there are other ways, for example, to remove all non alphanumeric characters from a string, but this may not give the desired result if you are working with telephone numbers.

Code:
Sub removechars2()
Dim rxp As Object, c As Range
Set rxp = CreateObject("vbscript.regexp")
With rxp
    For Each c In Selection
        .Pattern = "\W"
        .Global = True
        c = .Replace(c, "")
    Next
End With
End Sub
 
Upvote 0
You syntax might not be correct, but at least it makes sense :)

Code:
Sub removechars()
Dim cell As Range
For Each cell In Selection
    cell.Value = Application.WorksheetFunction.Substitute(ActiveCell.Value, "+", "")
    ' etc
Next cell
End Sub

Although there are other ways, for example, to remove all non alphanumeric characters from a string, but this may not give the desired result if you are working with telephone numbers.

Code:
Sub removechars2()
Dim rxp As Object, c As Range
Set rxp = CreateObject("vbscript.regexp")
With rxp
    For Each c In Selection
        .Pattern = "\W"
        .Global = True
        c = .Replace(c, "")
    Next
End With
End Sub

wow, thanks very much! I think i see now why I may have been getting that compile error. Good to know I was close.
 
Upvote 0
BTW the VBA native function equivalent to substitute is REPLACE, you don't need to call the WorksheetFunction.
 
Upvote 0
Thanks for the pointer teeroy.

so if i wanted to take things a step further now:

lets say i want to run this macro only on a range that contains the string "phone" in the name (in other words, something to search all column headers and then perform the macro only on the range named appropriately). What would that code look like? I can think through writing a formula to return something if it finds the string "phone", but I have no clue what that looks like in VBA. can I accomplish this? The ultimate goal is to be able to open a workbook containing client data and run the macro without changing the data at all.

also how would I add the logic to limit my range to the last row used in the data? If I highlight the range A:A and run the macro, it runs very very slowly (presumably because my selected range is 1 million rows).
 
Upvote 0
Would there just be one column with the string 'phone' or could there be several, if so should the code run on all of them?

On a typical workbook, how many rows of data would you expect?

On a small range, something based on the second example in my earlier reply might be practical if it can return the desired result, (what format are you expecting the numbers to be in after the code runs)?

With a larger range, your own method would be more practical, but, as teeroy suggested, using the replace function instead of substitute.
 
Upvote 0
could be multiple, may be only one. anywhere from 2000-50000 rows of data depending on the client im getting it from. end result should be of the form: 1111111111, no spaces, no extra characters, etc.

i just have no clue how to tell VBA to do:

search for (any) columns with string 'phone' in the name, perform operation on all rows up to the last row with data in that column, repeat for any appropriately named column.
 
Upvote 0
This is just air code, but it should get you moving in the right direction. I presume that you will have an idea where the column headers are - either hard-coded, or identifiable by inspection. One or many of those headers will contain the string "phone" (ie; "Phone Number"; "Telephone"; "Phone" etc). I will assume that you can select a cell in the appropriate header row, and that you want Excel to:
1) Read across that row and locate the columns of interest (ie, with 'phone' in the title)
2) Read down that column, and strip out the non-numeric characters - you already have the code to do so from the material above

I think you would end up with something like this:

Code:
Sub StripPhone()

Dim iHeaderRow        As Long
Dim rgStripCol           As Range
Dim rgHeaderCell       As Range
Dim rgStripCell          As Range

iHeaderRow = selection.row

for each rgHeaderCell in Intersect(Selection.EntireRow, ActiveSheet.UsedRange)
   if InStr(rgHeaderCell.value, "phone") + InStr(rgHeaderCell.value, "Phone") > 0 then
      set rgStripCol = Intersect(rgHeaderCell.EntireColumn, ActiveSheet.UsedRange)
      for each rgStripCell in rgStripCol
         if rgStripCell.row > iHeaderRow Then
             do whatever code will strip the non-numeric stuff
         end if
      next rgStripCell
   end if
next rgHeaderCell

End Sub
 
Last edited:
Upvote 0
See if this does what you need, please create a backup copy of your workbook first,

Code:
Option Explicit
Sub removechars()
Application.ScreenUpdating = False
Dim c As Range
With ActiveSheet
    For Each c In Intersect(.UsedRange, .Rows("1:1"))
        If InStr(LCase(c), "phone") Then
            With c.EntireColumn
                .Replace "(", ""
                .Replace ")", ""
                .Replace "+", ""
                .Replace "-", ""
            End With
        End If
    Next
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top