Hi All,
Just wondering if anyone can help, the code below works fine but I need it to check a defined range on every worksheet within the file. Can anyone help amend the code in order to do this?
The range that it needs to perform on is "D7:D35"
Just wondering if anyone can help, the code below works fine but I need it to check a defined range on every worksheet within the file. Can anyone help amend the code in order to do this?
The range that it needs to perform on is "D7:D35"
VBA Code:
Sub CleanSelectedRange()
' "Cleans" contents of all selected cells on the active worksheet
Dim Cell As Range
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = CleanString(Cell.Value)
End If
Next Cell
End Sub
Function CleanString(StrIn As String) As String
' characters, including carriage returns BUT NOT linefeeds.
' Does not remove special characters like symbols, international
' characters, etc. This function runs recursively, each call
' removing one embedded character
Dim iCh As Integer
Dim Ch As Integer 'a single character to be tested
CleanString = StrIn
For iCh = 1 To Len(StrIn)
Ch = Asc(Mid(StrIn, iCh, 1))
If Ch < 32 And Ch <> 10 Then
'remove special character
CleanString = Left(StrIn, iCh - 1) & CleanString(Mid(StrIn, iCh + 1))
Exit Function
End If
Next iCh
End Function
