Converting a certain range of columns from text to number format with VBA [duplicate]

Question

I get sent a spreadsheet weekly, and for various reasons beyond my control some columns come out as text stored as numbers rather than numbers. I need to convert them to numbers for stuff that happens with them later in the code.

I am converting them to numbers at the moment by doing this:

Dim rng As Range

For Each rng In Range("A:D").Columns
   rng.TextToColumns
Next rng

Is there a better (i.e. more efficient) way of doing this?

I played around with NumberFormat and it didn't seem to work.

Thanks in advance (and apologies if I have missed a solution already here - I did a search and didn't find it).

Answer 1

Excel:

• Copy an empty cell
• Select the range in which you need to change the format
• Select PasteSpecial, and under Operation select Add

---

VBA

(change Sheet1 Accordingly, assuming that A100000 is empty):

Sheet1.Range("A100000").Copy
Sheet1.UsedRange.PasteSpecial , xlPasteSpecialOperationAdd

If you place the above into the Workbook_Open event, it will perform the conversion automatically every time you open the workbook.

With this method, formulas are preserved.

---

I hope that helps!

Answer 2

From this website http://www.access-programmers.co.uk/forums/showthread.php?t=219316 I got this idea:

Range("D1:D400") = Range("D1:D400").Value

And it works perfectly!