help with code

[fundean]

The following code:

Public Function RemoveSpaces(strInput As String)
' Removes all spaces from a string of text
Test:
If InStr(strInput, " ") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, " ") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, " "))
GoTo Test
End If
End Function

will apprently remove spaces from a text string in an Excel spreadsheet. I would like to modify it so that it removes quotes from a text string, and removes it ONLY for a particular column (say, column A). when I replace the second parameter of the InStr function from " " to " "" ", it gives me a "stack overflow" error. Help !!!

fundean

[JustinLabenne]

How about just running a sub to kill it off?

Option Explicit
 
Sub KillTheQuotes()
    Dim szKill As String
    szKill = Chr(34)
    
    Dim r As Range
    
    For Each r In Range("A1:A100")
    
        r.Value = Replace(r.Value, szKill, Empty)
        
    Next r
    
End Sub

I set the range at A1-A100, doing a For Each loop through the entire column will be a bit slow, but this can be modified for a varying range if that's what you need.

[si_the_geek]

That's what I would recommend too, just with a couple of amendments:

You can set the range of your For loop to automatically go through all the rows in the sheet that have data in them, by using UsedRange.

Also, you should not use Empty as a value, you should use an empty string ( "" ) instead.

Option Explicit
 
Sub KillTheQuotes()
    Dim szKill As String
    szKill = Chr(34)
    
    Dim r As Range
    
    For Each r In Range("A1:A" & UsedRange.Rows.Count)
    
        r.Value = Replace(r.Value, szKill, "")
        
    Next r
    
End Sub

[JustinLabenne]

From msdn on using Empty:

Indicates that no beginning value has been assigned to a Variant variable. An Empty variable is represented as 0 in a numeric context or a zero-length string ("") in a string context.