CODEWITHSUNDEEP
excelvba
ingalcala
ingalcala

Reputation: 1845

Excel VB-Macro values from one column to only alphanumeric values on the next column

This is for an EXCEL Visual Basic Macro.

I need to change all column B data to only have alphanumeric characters on column C.

My code works only for one cell. I want to loop this code for every active cell. Can anybody help me on this?

Sub macroalphanum()
    Dim a$, b$, c$, i As Integer
    a$ = Range("C1").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("D1").Value = c$
End Sub

Upvotes: 0

Views: 754

Answers (1)

Julien Marrec
Julien Marrec

Reputation: 11905

You just need to nest your For Loop in another For Loop that goes through each cell

Quick Example:

Sub macroalphanum()
    Dim a$, b$, c$, i As Integer, r as Integer
    For r=1 to 100 step 1  ' This will do it for all rows from 1 to 100. Change it to suit your needs
       a$ = Cells(r,3).Value
       c$ = "" ' (Re)Initialize c
       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
    Cells(r,4).Value = c$
    Next r
End Sub

Upvotes: 2

Related Questions