A Painfull String operation taking ages to complete - Please help

[gagan_blue]

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

 

[Peter_SSs]

I'm not getting the behaviour you describe.

In B1:B10 I originally had what you see in D1:D10

After running your code from post #10, with B1:B10 selected, I get the results you now see in B1:B10.

Excel Workbook
	B	C	D
1	'23'		23
2	'15/04/15'		15-Apr-15
3	'eg'		eg
4	'gd'		gd
5	'48'		48
6	'dfsg'		dfsg
7	'15/03/10'		15/03/10
8	'dgff'		dgff
9	'dfsg'		dfsg
10	'dgfs'		dgfs
Apostrophe

 

[gagan_blue]

I'm not getting the behaviour you describe.

In B1:B10 I originally had what you see in D1:D10

After running your code from post #10, with B1:B10 selected, I get the results you now see in B1:B10.

Apostrophe

	B	C	D
1	'23'		23
2	'15/04/15'		15-Apr-15
3	'eg'		eg
4	'gd'		gd
5	'48'		48
6	'dfsg'		dfsg
7	'15/03/10'		15/03/10
8	'dgff'		dgff
9	'dfsg'		dfsg
10	'dgfs'		dgfs

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:118px;"><col style="width:35px;"><col style="width:95px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hi Peter,

In row#2 you got '15/04/15' with my macro based on variant array method. But if you run the macro in my opening post, you will get '15-Apr-15', because that macro reads cell.text and not cell.value.

So how do i get this behavior with varriant array method. Because v(variant array)=selection.text doesn't work.

 

[RoryA]

You can't without looping. You'd have to check the numberformat of each cell.

 

[Peter_SSs]

In row#2 you got '15/04/15' ...

Doh, I was sure I'd checked that cell. Never-the-less, it is not the behaviour your seemed to be describing. That is, my array is not storing the date as its serial number, rather it is storing it in the form of my system "Short date".

However, that doesn't solve your problem.

I understand your continued questioning about putting the Range.Text into the array, but that is not possible in a one-off operation.

With your very large range, you need to reduce the interaction between your code and the worksheet.
You original code interacted with the worksheet every cell and that is why is was very slow.
The array method that you have been trying lately only interacts with the worksheet twice, once to read into the array and once to transfer the results back to the worksheet. Very fast, but doesn't meet your need.

I don't think you need to check every cell - see if this compromise is satisfactory, interacting with the worksheet only when a date value is encountered.

I haven't done it, but the other thing I was wondering about is do you also need a check to skip it if the cell (& therefore array value) is blank?

I have assumed a rectangular selection.

Sub Add_Apostrophe()
  Dim v As Variant
  Dim i As Long, j As Long
  
  v = Selection.Value
  For i = 1 To UBound(v, 1)
    For j = 1 To UBound(v, 2)
      If IsDate(v(i, j)) Then
        v(i, j) = "''" & Selection.Cells(i, j).Text & "'"
      Else
        v(i, j) = "''" & v(i, j) & "'"
      End If
    Next j
  Next i
  Selection.Value = v
End Sub

here's my new results.

Excel Workbook
	B	C	D
1	'23'		23
2	'15-Apr-15'		15-Apr-15
3	'eg'		eg
4	'gd'		gd
5	'48'		48
6	'dfsg'		dfsg
7	'15/03/10'		15/03/10
8	'dgff'		dgff
9	'dfsg'		dfsg
10	'dgfs'		dgfs
Apostrophe

 

[gagan_blue]

Thanks Peter. Now my soul is at peace. I made one modification to your code since I want all dates in the same format i.e. "dd-mmm-yyyy". Still i wish this step was not required if Range.text could be transferred to array. But since it is not possible hence I am more than satisfied with the solution at hand. Thanks once again.

If IsDate(v(i, j)) Then

v(i, j) = format(v(i, j), "dd-mmm-yyyy")

End If

 

[Peter_SSs]

Still i wish this step was not required if Range.text could be transferred to array.

I don't see what the problem is - you are now back at the position of needing no worksheet interaction apart from loading the array and putting it back at the end. Happy days I'd say.

 

[gagan_blue]

I don't see what the problem is - you are now back at the position of needing no worksheet interaction apart from loading the array and putting it back at the end. Happy days I'd say.

The problem is that the below operation "If IsDate(v(i, j)) Then", although very fast in memory, but when executed for 700,000+ rows impacts the execution time. Basically I wanted to achieve maximum optimization but I think this is how far I can go. The limitation lies in vba, not our program/logic. Which is good to know. So yes Happy days.

If IsDate(v(i, j)) Then

 

[Peter_SSs]

The problem is that the below operation "If IsDate(v(i, j)) Then", although very fast in memory, but when executed for 700,000+ rows impacts the execution time.

True it does slow things down a bit - or quite a lot if there is a lot of dates. But even if you could have read the .Text value into the array, wouldn't you still have had to check every value for date in case it wasn't already in the "dd-mmm-yyyy" format?