VBA Fast Convert # to Text add ' Before Number

[VenturSum]

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:

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

 

[VenturSum]

Rick,

EVALUATE() ... That may be the answer.
As say be the array.
I'll test these strategies when I get to work.

BTW.. I love it when by boss says "How do you know so much??"
I smile and think of our Mr. Excel community.

Thank you,
John

 

[VenturSum]

Rick,

THANK YOU!! from 40 minutes to < 3 seconds.

Here is my final code, I used the sheet's code name
and adjusted the last row.

Sub PrefixAnApostophe()
Dim lRowLast As Long

    With wsTransactions
        lRowLast = .Cells(1, 17).End(xlDown).Row
        With .Range("Q2", "Q" & lRowLast)
              .Value = Evaluate(Replace("IF(@="""","""",""'""&@)", "@", .Address))
        End With
    End With
End Sub

Respectfully,

John in Annapolis