VBA Fast Convert # to Text add ' Before Number

VenturSum

Board Regular
Joined
May 23, 2010
Messages
138
Programmers,

I REALLY need your your Help speeding up some code.

In one worksheet a table has 70,000 rows.

In column "Q" (17), 40,000 cells have numbers; 30,000 are empty rows.

I need to convert these numbers into text
by adding a single quote (') before each number.

My code sorts the table in Column Q A-Z,
So the numbers are at the top
Then loop through each column:

Code:
lRow = 2
Do While .cells(lRow, 17).value <> ""

   .cells(lRow, 17).value  = "'" & .cells(lRow, 17).value 

   lRow = lRow + 1
Loop

Can you provide any other method to yield the same result??

Respectfully,

John Annapolis, MD
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
If you want to speed up, I will suggest using a For loop instead of a Do while.
Also, use the 'Application.ScreenUpdating = False' at the beginning of the code, and turn it back to 'True' at the end of the code.

Best,
Andy
 
Upvote 0
Why would a For be faster than a Do While loop?

Also, I am unsure if the data being held in a Table slows things down?


Both Screenupdating and AutoCalcutions are off.

Thank you,

John
 
Last edited:
Upvote 0
Hi John,
I haven't use the Do loops for years, so I can't remember the performance difference, and maybe there is no difference at all. When I learned how to use the For loops I never used Do anymore. Also I think that For Each Cell in Range.range is the correct and efficient way to loop through a range.

Try it and let us know if you notice any improvement. I would use this code.

Code:
Dim rCell as Range
Dim rRng as Range
dim uFila as Long

uFila = Range("Q" & rows.count).end(xlup).row 'this line look for the last row with value, so the loop do not run beyond the last cell with value

set rRng = Range("Q1:Q" & uFila)

For each rCell in rRng.Cells
   if isNumeric(rcell) then
       rcell.value = "'" & rcell.value
   end if
next rCell
 
Upvote 0
Andy,

Our coding is not too dissimilar :)
I'll try using both the Set Range and the For loop.

Btw: 40,000 rows took about 1 hour -- 11 / second!!
This is going through a Citrix network/server, which must also be slowing things down.

Respectfully,

John
 
Last edited:
Upvote 0
How about no loop?

Code:
  Range("Q2", Cells(Rows.Count, "Q")).TextToColumns _
      DataType:=xlFixedWidth, _
      FieldInfo:=Array(0, xlTextFormat)
 
Upvote 0
I need to convert the cells to text, because the cells contain job numbers.
E.g.:

15023
15045G
15245-003

When I copy the data to a new sheet,
Excel sometimes convert the cells that look like: 15254-002 into Exponent Notation. -> 152.54

So for sorting and copying it is best if everything looks like text with a preceding single quote: '15254-002.

Respectfully,

John in Annapols, MD
 
Last edited:
Upvote 0
Give this macro a try and see if it is any faster...
Code:
[table="width: 500"]
[tr]
	[td]Sub PrefixAnApostophe()
  With Range("Q2", Cells(Rows.Count, "Q").End(xlUp))
    .Value = Evaluate(Replace("IF(@="""","""",""'""&@)", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Give this macro a try and see if it is any faster...
Code:
[table="width: 500"]
[tr]
	[td]Sub PrefixAnApostophe()
  With Range("Q2", Cells(Rows.Count, "Q").End(xlUp))
    .Value = Evaluate(Replace("IF(@="""","""",""'""&@)", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]
And if the above is still slow, then maybe this would be faster...
Code:
Sub PrefixAnApostophe()
  Dim R As Long, Arr As Variant
  Arr = Range("Q2", Cells(Rows.Count, "Q").End(xlUp))
  For R = 2 To UBound(Arr)
    If Len(Arr(R, 1)) Then Arr(R, 1) = "'" & Arr(R, 1)
  Next
  Range("Q2").Resize(UBound(Arr)) = Arr
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

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