VBA Find and Replace

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I need to do the following in VBA as it is part of a bigger project.

I need to do a find and replace on , , and replace with ,

The problem is there could be multiple comma spaces, such as

, , ,

now that above Find and Replace would change that to , ,

If I was doing this manually I would repeat the Find and Replace until it didn't find anymore.

How can I do this in VBA?

So far this is what I have as a test for this:

Code:
Sub test()
Cells.Replace ", ,", ", "
End Sub

It doesn't however generate an error when it doesn't find any, how can I repeat the code as long as it is necessary?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
If you know it will only be needed a maximum of 10 times, just use a quick loop:
Code:
Sub test()
For i = 1 To 10
    Cells.Replace ", ,", ", "
Next i
End Sub
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Yeah, I thought of that, but I have no way to know how many times it would be necessary. If I go to high, it's horribly inefficient, and if I go to low, then my data is not cleaned up as it should be. I may still go with that, I was just hoping there was some way to know when it couldn't find the search terms any longer.

I think I may have just figured it out, probably something along the lines of a Do Loop coupled with a Find so that it loops only as long as it finds the value.
 
Last edited:

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This is what I came up with:

Code:
Sub test()
Dim c As Range
Do
    Set c = Cells.Find(", ,")
    Cells.Replace ", ,", ","
Loop Until c Is Nothing
End Sub
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi Hotpepper

You can also do it without the range object:

Code:
Sub test()
While Not Cells.Find(", ,") Is Nothing
    Cells.Replace ", ,", ","
Wend
End Sub
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Hey Hotpepper...

I can't help but wonder where the data is coming from?
Is your bigger project creating the data that you need to do the replace on?
Or is it raw data you have recieved and have no control over?

I would think it may be easier and more effective to get the problem at the source...
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

It is raw data I have no control over

Thanks pgc01, that worked really well! :)
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
I know this is a day late and a dollar short, but...

does your data have any actual spaces (other than the spaces between the commas)?
I mean, does any of the ACTUAL data contain spaces?

If not, you could try something like this

Code:
x = Replace(Range("A1").Value, ",", " ")
x = Application.Trim(x)
x = Replace(x, " ", ",")
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Yes the actual data contains spaces, and it essentially needs to be applied to the entire spreadsheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,430
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top