How to call code for multiple columns

Question

I need to run the following code on non-contiguous columns, There is code posted here on how to do this for a different piece of code. Add loop to vba code for excel

I Need to do the same for:

Sub CleanAll()
    Dim rng As Range

    For Each rng In Sheets("Sheet1").Range("D2:D100").Cells 'adjust sheetname and range   accordingly
        rng.Value = TextOnly(rng.Value)
    Next
End Sub

My failed effort

Sub CleanAll(sColRange As String)
    Dim rng As Range

    For Each rng In Sheets("Sheet1").Range(sColRange).Cells 'adjust sheetname and range accordingly
        rng.Value = TextOnly(rng.Value)
    Next
End Sub

use like this:

Call CleanAll("B2")
Call CleanAll("H2")

Thanks

Edit

The above calls the following

Function TextOnly(strSource As String) As String
    Dim i As Integer
    Dim strResult As String

    For i = 1 To Len(strSource)
        Select Case Asc(Mid(strSource, i, 1))
            Case 48 To 57, 65 To 90, 97 To 122: 'include 32 if you want to include space
                strResult = strResult & Mid(strSource, i, 1)
        End Select
    Next
    AlphaNumericOnly = strResult
End Function

What's the problem? Your edited version seems like it should work, so maybe it's how you're calling it? Maybe you should try something like `CleanAll "B2,H2"` — Tim Williams, Apr 26 '13 at 20:24
This works fine the way you are doing it, what's your actual code which isn't working? What is `TextOnly`? — enderland, Apr 26 '13 at 20:33
Yes, from here: http://stackoverflow.com/questions/15723672/how-to-remove-all-non-alphanumeric-characters-from-a-string-except-period-and-sp — xyz, Apr 26 '13 at 20:50
The apparent problem is that `TextOnly` is not defined anywhere. In your subroutine called `CleanAll()` you are calling a function named `TextOnly`. Yet you are providing a function called `AlphaNumericOnly`. Does it work, if you revise it to call the proper `AlphaNumericOnly` function? — David Zemens, Apr 27 '13 at 03:04

score 2 · Accepted Answer · answered Apr 27 '13 at 01:43

why don't simple pass range to subroutine?

Sub CleanAll(range as Range)
    Dim rng As Range

    For Each rng In range.Cells 
        rng.Value = TextOnly(rng.Value)
    Next
End Sub

then you can call it by

CleanAll(Sheets("Sheet1").Range("A1:A100"))

How to call code for multiple columns

1 Answers1