A Painfull String operation taking ages to complete - Please help

gagan_blue

New Member
Joined
Sep 30, 2014
Messages
37
Hi Forum - Salutations to the excel masters, MVPs.

Looking to seek your help with something giving me pain from a long time. Very often I need to append apostrophe (') character to a column containing 700,000+ rows. So i have written this simple macro to do the job. But the problem is it takes lot of time (2-5 minutes).

Therefore I wanted to get your opinion if there is an efficient and faster way to do this. Maybe instead of cell by cell operation, is there a way to do a range operation - Range = "'" & Range & "'".

Sub AddApostrophie()
Dim cell As Range

For Each cell In Selection
cell.Value = "''" & cell.Text & "'"
Next cell

End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Look at the Range.Replace Method in VBA Help.
 
Upvote 0
Look at the Range.Replace Method in VBA Help.
On second thought, that won't work. I would put the selection into a variant array (call it V), V =Selection.Value, then loop through the items in the array, change them and then go back (Selection.Value = V). The item by item changes are all done in memory which is very fast compared to what you are currently doing.
 
Upvote 0
Negative. Range.replace will replace an existing character/string with new character/string. In my case there is nothing to replace. Need to concatenate apostrophe "'" to the start and end of text.
 
Upvote 0
JoeMo, Thank you soooooooooo much. did it with Variant array. Time reduced from 49 seconds to 2 seconds.

Sub AddApostrophie2()
StartTime = Timer
Dim cell As Range
Dim v As Variant

v = Selection.Value

Dim i As Long

For i = 1 To UBound(v)

v(i, 1) = "''" & v(i, 1) & "'"


Next i

Selection.Value = v
SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation


End Sub
 
Upvote 0
On second thought, that won't work. I would put the selection into a variant array (call it V), V =Selection.Value, then loop through the items in the array, change them and then go back (Selection.Value = V). The item by item changes are all done in memory which is very fast compared to what you are currently doing.

JoeMo- Got into a situation when using the Variant array method. My earlier method, which was a cell by cell operation, was able to handle dates properly. Because I could use the cell.text property which returns formatted date rather than serial number. But with when I use v(variant array) = selection.text it just doesn't work. Any thoughts how i can overcome this?
 
Upvote 0
Negative. Range.replace will replace an existing character/string with new character/string. In my case there is nothing to replace. Need to concatenate apostrophe "'" to the start and end of text.


If i use v = selection.value
typename(v) returns variant

but if i use v = selection.text
typename(v) returns string.
 
Upvote 0
For the code you gave in post #5, what exactly is it doing incorrectly with dates? Examples?
 
Upvote 0
JoeMo- Got into a situation when using the Variant array method. My earlier method, which was a cell by cell operation, was able to handle dates properly. Because I could use the cell.text property which returns formatted date rather than serial number. But with when I use v(variant array) = selection.text it just doesn't work. Any thoughts how i can overcome this?
Assuming you will always be specifying the range by selecting it (we can have VB do that for you if you tell us the starting cell address), will your selection always contain only formatted dates? Also, what is the format of those dates?
 
Upvote 0
For the code you gave in post #5, what exactly is it doing incorrectly with dates? Examples?
Hi Peter,

My objective is to concatenate apostrophe ("'" ) to the beginning and end of a large number of cells (mix of numbers, dates, text). So I am transferring entire range to a variant array. But while doing that, dates are stored as serial number in the variant array. I need to store the dates in their original format eg "15-Apr-1986". I was able to do that with my earlier macro (mentioned in my opening post) where I was using cell.text property. But that macro was very slow so i dont want to use that.

So bascially why cant i use v(variant array) = selection.text

Sub AddApostrophie()

StartTime = Timer

Dim cell As Range
Dim v As Variant

v = Selection.Value


Dim i As Long

For j = 1 To UBound(v, 2)

For i = 1 To UBound(v, 1)

v(i, j) = "''" & v(i, j) & "'"

Next i

Next j

Selection.Value = v

SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "Time taken" & SecondsElapsed & " seconds", vbInformation


End Sub
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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
Back
Top