Expand macro that removes special characters to run through entire column

lizzie5 · May 9th 2012

Hi all, I'm hoping to get some help expanding a REALLY useful macro I found in another thread. The code below takes a value and removes any special characters (including spaces) and outputs the result in the neighboring column.

Code

Sub SpecialCharacters()'removes all special characters from contents of column A, INCLUDING spaces
'outputs value in column B




Dim a$, b$, c$, i As Integer
     
a$ = Range("A1").Value
    For i = 1 To Len(a$)
        b$ = Mid(a$, i, 1)
        If b$ Like "[A-Z,a-z,0-9]" Then
            c$ = c$ & b$
        End If
    Next i
Range("B1").Value = c$




End Sub

Display More

I can't figure out how to expand the macro to be able to run through any/all values in column A. Currently, as you can see, the macro refers to specific cells and takes the value of A1, then outputs the result into cell B1. My column A length will vary and I need this code to be able to run through column A as long as there is a value there, and then output the result into the corresponding cell in column B.

Hopefully I made sense here. I attached a sample document illustrating what I'm asking for, the doc also includes the code posted here. Thanks to all for your help!!

jindon · May 9th 2012

Re: Expand macro that removes special characters to run through entire column

try

Code

Sub test()
Range("a1", Range("a" & Rows.Count).End(xlUp)) _
.Offset(, 1).Formula = "=CleanAll(a1)"
End Sub




Function CleanAll(ByVal txt As String)
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[^A-Za-z\d]"
    CleanAll = .Replace(txt, "")
End With
End Function

Display More

**Trebor76** · May 9th 2012

Re: Expand macro that removes special characters to run through entire column

Hi lizzie5,

See how this goes:

Code

Option Explicit
Sub Macro1()


    'http://www.ozgrid.com/forum/showthread.php?t=165208


    Dim rngCell As Range, _
        rngMyData As Range
    Dim intCellPos As Integer
    Dim strMyValue As String
    
    Application.ScreenUpdating = False
    
    Set rngMyData = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) 'Data set [A1:A - last row in Col. A] to run macro on. Change to suit.
    
    For Each rngCell In rngMyData
        strMyValue = "" 'Initialise variable
        For intCellPos = 1 To Len(rngCell)
            If Mid(rngCell, intCellPos, 1) Like "[A-Z,a-z,0-9]" Then
                strMyValue = strMyValue & Mid(rngCell, intCellPos, 1)
            End If
        Next intCellPos
        rngCell.Offset(0, 1).Value = strMyValue
    Next rngCell
    
    Application.ScreenUpdating = True


End Sub

Display More

HTH

Robert

lizzie5 · May 9th 2012

Re: Expand macro that removes special characters to run through entire column

Many thanks to BOTH of you for the quick replies! Both macros work great...much appreciated!

**Trebor76** · May 9th 2012

Re: Expand macro that removes special characters to run through entire column

Glad to have helped

Expand macro that removes special characters to run through entire column

Participate now!

Share