vba macro running VERY slow

[RC]

Any help is greatly appreciated.
I'm not big on writing these, I usually take something I have and modify it,
so please keep it simple.
As you can see from the included vb all I'm trying to do is replace a
specific character and remove the CRLF. These are causing a major problem
when importing into one of my other applications. It works fine on one or a
small group of cells, but if I select the entire sheet....well last time
after 30 minutes I just gave up. What am I doing wrong?

Sub ClearCRLFComma()
For Each Cell In Selection
temp = Cell.Text
For i = 1 To Len(temp) Step 1
a = Mid(temp, i, 1)
If a = vbCrLf Or a = vbCr Or a = vbLf Then
Mid(temp, i, 1) = " "
End If
If a = "," Then
Mid(temp, i, 1) = "-"
End If
Next i
Cell.Value = temp
Next
End Sub

In a perfect world I'd want a single button macro that did all of the
following;
What' in the above vb.
Remove (delete) all text that is bold (this removes a bunch of meaningless
labels).
Reformat the entire sheet as "General" or "Text" (no "Currency", "Number",
or "Date")
Swap the values of two cells in a row only if there is a value in the second
cell (i.e. A2=x and A3 is blank then do nothing. But if A2=x and A3=y then
swap the values so that A2=y and A3=x).
And then save it as a .csv file.

If anyone has any suggestion on what I can do (keep it clean) to accomplish
this, let me know.
Thanks in advance
RC

 

[Tushar Mehta]

A powerful tool that few leverage is access to the native capabilities
of Office applications through their respective object models.

In this case, XL has the native ability to replace text tokens.

In XL, select a range, turn on the macro recorder (Tools | Macro >
Record new macro...), and replace something by itself (after all, the
purpose of this exercise is to get XL to generate the correct code for
a replace -- any replace). Turn off the recorder. Switch to the VBE
and you'll find the code:

Selection.Replace What:="a", Replacement:="a", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Replace the what "a" with vbcrlf. Replace the Replacement "a" with
" " to get
Selection.Replace What:=vbCrLf, Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Copy this and change the What and Replacement parts to work with vbCr,
vbLf and ",' with "-" to get four replace statements.

I am not clear on the bolded text piece as to whether all the text in a
cell is bold or only some characters. In either case, use the macro
recorder in XL and check the code you get.

Similarly, the use of the macro recorder while changing the format for
all the cells will give you a two line solution.

As far as swapping the contents of two cells goes, you refer to 2 cells
in a row but then use A2 and A3 (which are in two different rows) in
your example.

Do keep in mind one thing. The macro recorder does a lot of select's
and activate's that are not necessary. For a mini-tutorial on how to
get rid of them see the Excel | VBA | Beyond Excel's Recorder page of
my web site.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[RC]

Thanks for the help, but I already tried the find/replace as you suggested.
When I use it I get an error about the formula being too long. This is a
known issue with excel, when the cell contents exceed a certain number of
characters you get this error and the find/replace stops.

I was brain storming on this the other day with a friend and came up with a
few ideas, hopefully I'll have time to get bake on that project soon. Here's
what we came up with. While not completely automated it should keep the
number of steps to a minimum.

The bold text is mostly section headings that will screw up the import.
These we clear manually by first sorting the sheet (all the other entries in
column A are numbers so these end up nicely grouped).

Now I run the macro.

The first part will do the find and replace as originally written but will
no run it on the entire sheet. I'll define a selection of cells with enough
extra to account for growth, but not waste time by running it to the limits
of possible XL cells.

The next section will insert 2 columns, then based on the contents or lack
of contents of the 2 columns that swap (when they swap there are values in
both, when they aren't swapped only one has a value) I'll copy the correct
value into the correct new column. I'll just ignore the original two columns
on my import script.

The whole process leaves me with about 3 manual steps, but I think that's
the best I'm going to be able to do until/unless I spend a lot of time
getting up to speed on VB.

Thanks again for you suggestion

RC