Text to numeric issue

[liz]

How can I get Excel to recognize numeric fields as
numbers? Here is the situation. I am importing data from a
SQL Server thru a DTS package. In each loop the Excel row
output is going to be different. For example, loop 1 may
have 20 rows, loop 2 50 rows, etc. The column number stays
the same. If I use the multipy by 1 option for each
column, Excel adds 0's to the end of the spreadsheet. For
example, 1-20 are conversted to numeric, then 21-65,000
now contain a 0 for this colomn. Then formatting for
printing is a nightmare, actually impossible. I need some
automated way, I can even use a macro, to change the text
cells to numeric. Once again, the issue is the number of
rows is differnt on each spreadsheet.(Also, I can't select
and use the new text to number function - diffent number
of rows.)

I hope this makes sense.

 

[Dave Peterson]

Instead of using 1 and paste special|multiply, you can use an empty cell and
paste special|Add.

This'll get rid of all those extra 0's, but it'll still extend your range for as
many rows as you selected (all of them when you do the whole column).

Debra Dalgleish has posted a couple of techniques for fixing this after the
fact:

http://groups.google.com/[email protected]

This was the code by Jon Peltier that Debra posted:

Sub ConvertToNumbers()
'by Jon Peltier
Cells(65535, 255).Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationAdd
End Sub

You could modify it slightly to just get your column:

Option Explicit
Sub ConvertToNumbers2()

Dim myRng As Range
Const myCol As String = "C"

With ActiveSheet
Set myRng = .Range(.Cells(1, myCol), _
.Cells(.Rows.Count, myCol).End(xlUp))
.Cells(65535, 255).Copy
myRng.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationAdd
End With

End Sub

You specify the column letter and the code will start in the first row and come
up from the bottom to get the last row of that column.