Remove non-numbers in Column F

[Livin404]

Hello the following code I used in the past on another project. In this case I need to change it so it will start on Row 5 of Column F and the range will depend on the height of my table. I do not need a input box.

Sub RemoveAllNonNums_Macro()
Dim myR As Range
Dim myRange As Range

Set myRange = Application.Selection
Set myRange = Application.InputBox("select one range that you want to remove non-numeric characters", RemoveAllNonNums, myRange.Address, Type:=8)
For Each myR In myRange
    myOut = ""
    For i = 1 To Len(myR.Value)
        tmp = Mid(myR.Value, i, 1)
        If tmp Like "[0-9]" Then
            myStr = tmp
        Else
            myStr = ""
        End If
        myOut = myOut & myStr
    Next i
    myR.Value = myOut
Next

End Sub

 

[DanteAmor]

In this case I need to change it so it will start on Row 5 of Column F

Try this:

Sub RemoveAllNonNums_Macro()
  Dim c As Range
  
  With CreateObject("VBScript.RegExp")
    .Pattern = "[^0-9]"
    .Global = True
    For Each c In Range("F5", Range("F" & Rows.Count).End(3))
      c.Value = .Replace(c.Value, "")
    Next
  End With
End Sub

 

[Peter_SSs]

If your data happens to be large, this would be a bit quicker due to not interacting with the worksheet each row.

Sub KeepDigitsOnly()
  Dim a As Variant
  Dim i As Long
  
  With Range("F5", Range("F" & Rows.Count).End(xlUp))
    a = .Value
    With CreateObject("VBScript.RegExp")
      .Global = True
      .Pattern = "\D"
      For i = 1 To UBound(a)
        a(i, 1) = .Replace(a(i, 1), "")
      Next i
    End With
    .Value = a
  End With
End Sub

 

[DanteAmor]

If it were the case with lots of data, here is my proposal:

Sub RemoveAllNonNums_Macro()
  Dim i As Long, a As Variant
  
  a = Range("F5", Range("F" & Rows.Count).End(3)).Value
  With CreateObject("VBScript.RegExp")
    .Pattern = "[^0-9]"
    .Global = True
    For i = 1 To UBound(a, 1)
      a(i, 1) = .Replace(a(i, 1), "")
    Next
  End With
  Range("F5").Resize(UBound(a, 1)).Value = a
End Sub

 

[Peter_SSs]

If it were the case with lots of data, here is my proposal:

Is that any different to my proposal?
- read the data into an identical array
- use an identical reg ex pattern - non-digit (just written with different syntax)
- process the array row-by-row in an identical way
- put the results back in the identical range

 

[Livin404]

Try this:

Sub RemoveAllNonNums_Macro()
  Dim c As Range
 
  With CreateObject("VBScript.RegExp")
    .Pattern = "[^0-9]"
    .Global = True
    For Each c In Range("F5", Range("F" & Rows.Count).End(3))
      c.Value = .Replace(c.Value, "")
    Next
  End With
End Sub

That was perfect! Thank you!

 

[Livin404]

If your data happens to be large, this would be a bit quicker due to not interacting with the worksheet each row.

Sub KeepDigitsOnly()
  Dim a As Variant
  Dim i As Long
 
  With Range("F5", Range("F" & Rows.Count).End(xlUp))
    a = .Value
    With CreateObject("VBScript.RegExp")
      .Global = True
      .Pattern = "\D"
      For i = 1 To UBound(a)
        a(i, 1) = .Replace(a(i, 1), "")
      Next i
    End With
    .Value = a
  End With
End Sub

Thank you for reaching out. I feel so spoiled sometimes to have choices. Much appreciated.

 

[Rick Rothstein]

*** IGNORE - I misunderstood the question ***

Assuming your values are all constants (no formulas), what about this one-liner...

Sub RemoveNonNumbers()
  Range("F5", Cells(Rows.Count, "F").End(xlUp)).SpecialCells(xlConstants, xlTextValues).ClearContents
End Sub

 

[Peter_SSs]

Assuming your values are all constants (no formulas), what about this one-liner...

Sub RemoveNonNumbers()
  Range("F5", Cells(Rows.Count, "F").End(xlUp)).SpecialCells(xlConstants, xlTextValues).ClearContents
End Sub

OP seemed to be asked for the same operation as the original code, just the range determined in a different way?

 

[Rick Rothstein]

OP seemed to be asked for the same operation as the original code, just the range determined in a different way?

I was just offering him what I thought is an interesting (and more compact) alternative in case he might like to make use of it.