using replace method

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Using replace method

Using replace method

resolvedResolved · Medium Priority · Version 2016

Diane has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course

Using replace method

Hi Jens
I am sorry to be a pain, but I have another question. I want to replace any commas found in all cells in Column A with a space. There are spaces in other columns in my spreadsheet, but I don't want them affected. I am running this code, but despite selecting column A only (I am stepping thru and know it is only selecting A), it still changes any comma anywhere in the sheet. If I do the same Search and replace manually (from CTRL & A dialog box), that does precisely what I want. Can you see what I might be doing wrong? The other two replaces I am doing are fine as the text to be replaced only appears in column A anyway.

Kind Regards


iNoOfRows = Cells(Rows.Count, 1).End(xlUp).row - 1

Dim sFindTextComma As String
Dim sfindTextStop As String
Dim sFindTextUHL As String

Dim sReplaceTextComma As String
Dim sReplaceTextHD As String
Dim sReplaceTextSpc As String

Dim sEndrow As String

sFindTextComma = ","
sfindTextStop = "."
sFindTextUHL = "(UHL)"

sReplaceTextComma = ""
sReplaceTextHD = "Hotdesk"
sReplaceTextSpc = ""

sEndrow = "a" & iNoOfRows
Range("a2", sEndrow).Select

Cells.Replace what:=sFindTextUHL, replacement:=sReplaceTextSpc, _
lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False, _
searchformat:=False, ReplaceFormat:=False

Cells.Replace what:=sfindTextStop, replacement:=sReplaceTextHD, _
lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False, _
searchformat:=False, ReplaceFormat:=False

Cells.Replace what:=sFindTextComma, replacement:=sReplaceTextComma, _
lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False, _
searchformat:=False, ReplaceFormat:=False

End Sub

RE: using replace method

Hi Diane,

Hopefully I can help. Jens is training on site today.

I've had a look at your code and the first thing I can see is that your replace text is "". Should it be " " if you want to insert a space?

Getting the obvious, easy stuff out of the way.

Please let me know if that helps and we can dig deeper.


RE: using replace method

Hi Diane,

I looked a bit harder at this.

I think you need selection.replace and not cells.replace. All the rest of your code is fine - well fine according to my test mock up...

Cells.whatever will apply the property or method to the whole worksheet. Cells(x,y) will pick out a particular range. That's why you're replacing items outside of the range you selected.

Let me know if this is the bottom of the problem.


RE: using replace method

Hi Clare

Sorry I mislead you. I was replacing with a space, but then decided on no space. But whatever I put in the replace value (at one point I put a series of XXXXX's as thy were easier to spot), it always replaces the comma throughout the sheet with the replace value when my expectation is that it will only affect the range I have selected in column A.

Kind Regards

RE: using replace method

Thanks Claire, saw your other reply after I relied to the first. I will give that a go

RE: using replace method

Hi Clare

Perfect thank you. You guys are unbelievable. Nothing ever seems too much trouble and you are such a joy to deal with.

Can you please pass my comments on to Jens and to your management team. I really would not to hesitate to recommend STL. you are much better than companies that before I have perceived to be market leaders.

(But I hope I am not making a nuisance of myself with too many questions :) )



Training courses


Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


Excel tip:

Display developer tab - Excel 2010

a. In Excel, click on the File tab
b. Select Options from left hand side
c. Choose the Customize Ribbon section
d. Click the box next to Developer in the list of tabs on the right hand side of the dialog box. When ticked the Developer tab will be visible.
e. Click OK to apply your changes

View all Excel hints and tips

Server loaded in 0.08 secs.