Inputbox to select the range from user to apply a specific function.

[imran1059]

Hi All,

I have following VBA code to remove extra hidden characters from a range. It works fine. But many times I need to change the range for removing extra hidden characters and for this everytime I have to go edit the macro. I want an inputbox to be appeared to select (from user) the range upon which my vba code will be applied. Please help.

Sub CleanAll()
Dim rng As Range


For Each rng In Application.ActiveSheet.Range("A1:D1000").Cells
rng.Value = AlphaNumericOnly(rng.Value)
Next
MsgBox ("Fixed")
End Function

 

[dmt32]

Hi,
untested but try

Sub CleanAll()
    Dim rng As Range, cell As Range
    
    On Error Resume Next
    Set rng = Application.InputBox("Select Range", "Range Selection", , , , , , 8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    
    
    For Each cell In rng.Cells
        cell.Value = AlphaNumericOnly(cell.Value)
    Next cell
    
    MsgBox "Fixed", 48, "Fixed"
End Sub

Dave

 

[imran1059]

Hi Dave,

it worked,

Thanks a lot,
Imran.

 

[dmt32]

Hi Dave,

it worked,

Thanks a lot,
Imran.

welcome - thanks for feedback

Dave