![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Microsoft 365 and Office | Excel | For home | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Hello nekiaholland,
Firstly you can delete all of the ScrollRow lines that gets inserted when recording code. I am not clear on exactly where you need the code to convert the data from text to numeric because at the end of your code you select worksheet Data. Therefore I have commented out your code so I could simply test the conversion code and will leave it to you to decide if it goes before selecting worksheet data.
Just so you understand what is occurring, the code firstly sets the Numeric format of columns A, B and C to General. (could use numeric but General works). This does not convert the text to numeric at this stage but prepares the columns to accept numeric values.
Then we find a blank cell on the worksheet and enter 1 (one) in it. (It is the first blank cell below the data in column A.
Then copy the cell with the 1 in it and then PasteSpecial -> Multiply over the data in columns A, B and C. Performing a mathematical operation on the data converts it to numeric. However, multiplying by 1 does not actually change the numeric value.
The User Defined Function (UDF) LastRowOrCol is code that very reliably finds the last row on the worksheet that contains data. Other methods can be used but if some columns are longer than others then it is possible that the last row is not found.
Sub ConvertToNumeric()
Dim lngLastRow As Long
'Sheets("Sheet1").Select
'Range("D4").Select
'Columns("A:A").Select
'Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'Columns("E:E").Select
'Selection.Cut Destination:=Columns("A:A")
'Columns("E:E").Select
'Selection.Delete Shift:=xlToLeft
'Range("A2:C356").Select
'Range("A1").Select
'Sheets("DATA").Select
'Range("D4").Select
'Format the required columns to General (or numeric)
Columns("A:C").NumberFormat = "General"
'Find an empty cell below the data
lngLastRow = LastRowOrCol(ActiveSheet, True)
'Insert 1 in the empty cell
Range("A" & lngLastRow + 1) = 1
'Copy the cell with 1 in it
Range("A" & lngLastRow + 1).Copy
'PasteSpecial with Multiply to the range of text numbers
Range("A2:C" & lngLastRow).PasteSpecial _
Paste:=xlPasteAll, _
Operation:=xlMultiply, _
SkipBlanks:=False, _
Transpose:=False
'Cleanup. clear the cell with 1
Range("A" & lngLastRow + 1).ClearContents
End Sub
Function LastRowOrCol(ws As Worksheet, bolRowCol As Boolean, Optional rng As Range) As Long
'Finds the last used row or column in a worksheet
'First parameter is Worksheet
'Second parameter is True for Last Row or False for last Column
Dim lngRowCol As Long
Dim rngToFind As Range
If rng Is Nothing Then
Set rng = ws.Cells
End If
If bolRowCol Then
lngRowCol = xlByRows
Else
lngRowCol = xlByColumns
End If
With ws
Set rngToFind = rng.Find(What:="*", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=lngRowCol, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
End With
If Not rngToFind Is Nothing Then
If bolRowCol Then
LastRowOrCol = rngToFind.Row
Else
LastRowOrCol = rngToFind.Column
End If
End If
End Function