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

 

[ivanwakeup]

teeroy - this is awesome, runs very quickly! thanks

ok, so now i've been messing with a few versions of these solutions to try and add some stuff and i'm totally stuck on implementing something new. Here is the code i'm working with atm:

Sub removechars()Application.ScreenUpdating = False
Dim c As Range
Dim c1 As Range
     
     
With ActiveSheet
    For Each c In Range("a1:q1")
        If InStr(LCase(c), "phone") Then
            With c.EntireColumn
                .Replace "(", ""
                .Replace ")", ""
                .Replace "+", ""
                .Replace "-", ""
            End With
        End If
    Next
    
 
     
End With


With ActiveSheet
     For Each c In Range("a1:q1")
        If InStr(LCase(c), "phone") Then
        Set c = .EntireColumn
        If Len(c) > 10 Then
            c = Right(c, 10)
        End If
        
End With


Application.ScreenUpdating = True
End Sub

basically what i'm trying to do is now also determine if, within any rows titled "phone" the count of characters within each cell is greater than 10 (this occurs when my source data has a 1 before the rest of the number, which i need to remove). if it is greater than 10, i just want the right 10 characters. am i even close to getting this to do what i want?

I think it's time for me to hit the books because this isn't as easy to pick up as i thought

 

[ivanwakeup]

ok, so i've gotten the code this far:

Sub removechars()Application.ScreenUpdating = False
Dim c As Range
Dim c1 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


 For Each c In Range("a2:a2345")
        If Len(c) > 10 Then
            c = Right(c, 10)
        End If
 Next
 
     
End With




Application.ScreenUpdating = True
End Sub

but now what i'm not understanding is how to make my range for my second loop dynamic. rather than having to specify my range, i want to tell it to point to the column range with "phone" in the name.

 

[jasonb75]

You can't use Right() or similar with ranges, only single cells.
I'm going to think on this for a bit but at the moment all I have is looping through individual cells.

 

[jasonb75]

this should work, Teeroy might have an alternative method that I'm not thinking of.

Sub removechars()
Application.ScreenUpdating = False
Dim c As Range, c1 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 "-", ""
                For Each c1 In .Cells.Offset(1)
                    If Len(c1) > 10 Then c1 = Right(c1, 10)
                Next
            End With
        End If
    Next
End With
Application.ScreenUpdating = True
End Sub

 

[dcardno]

I believe that jasonb75 is correct - you will have to address this cell by cell. I believe that there is another problem in what you have so far - you will be stripping values out of the header, as well (and you might include spaces in the characters to be stripped, in case you get telephone numbers formatted as (604) 555-1234; I believe what you have now will return "604 5551234").

I would suggest that when you find a column of interest you store the value of that header for that column in a string variable, perform the replacements as you have done, then loop through each cell in the range
Intersect(c.entirecolumn, UsedRange)
to take only the right-most ten characters, and then replace the header cell with the value stored in the string variable.

 

[ivanwakeup]

Sub removechars()
Application.ScreenUpdating = False
Dim c As Range
Dim c1 As Integer


     
     
 
With ActiveSheet
    For Each c In Intersect(.UsedRange, .Rows("1:1"))
        If InStr(LCase(c), "phone") Then
            c1 = c.Column
            With c.EntireColumn
                .Replace "(", ""
                .Replace ")", ""
                .Replace "+", ""
                .Replace "-", ""
                .Replace " ", ""
            End With
        
        End If
    Next


 For Each c In Intersect(.UsedRange, .Columns(c1))
        If Len(c) > 10 Then
            c = Right(c, 10)
        End If
 Next
 
     
End With




Application.ScreenUpdating = True
End Sub

finally got it working. didn't realize i needed to set a new variable (c1) = c.column. dcardno, you're right, i need to account for ALL possible incorrect characters in my first loop, so i think i need a way to just remove everything that isn't alphanumeric, starting in the cell AFTER the header.

 

[dcardno]

I think processing the entire column will be significantly faster than going cell-by-cell; hence the suggestion to hold the (existing) header value in a variable, let the routine strip out all the special characters, and then replace it.

 

[Teeroy]

Go back and look at post #20 again. It already sets a dynamic Range which selects the Column with "Phone" in Row 1-4 in variable Rng.

I think you overlooked the biggest time saver in my previous post which was using the Find method rather than looping and using instr to get the "Phone" column.

The best way to remove everything non-alphanumeric characters would be to use a "Regular Expression" but these can be tricky; they are extremely powerful but can lead to unpredictable results if you are not careful with the data and pattern match.

 

[jasonb75]

I think you overlooked the biggest time saver in my previous post which was using the Find method rather than looping and using instr to get the "Phone" column.

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.

The best way to remove everything non-alphanumeric characters would be to use a "Regular Expression" but these can be tricky; they are extremely powerful but can lead to unpredictable results if you are not careful with the data and pattern match.

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.

 

[ivanwakeup]

yes Jason, you are correct... there can be multiple columns with phone that the operation should be performed on.

Teeroy- can i use your method to both clean characters from the entire column and also perform right(10) on each cell within that same column?