Delete text from column C

[Abhishekghorpade]

Hi,

Please give me macro to delete all text from column C... I have cells with numbers that should be untouched.

Thanks

 

[GR00007]

This is a function that returns only numbers...

Function NumbersOnly(r As Range) As String' NumbersOnly Macro
vOut = ""
vStr = r.Formula
For i = 1 To Len(vStr)
  If Mid(vStr, i, 1) >= "0" And Mid(vStr, i, 1) <= "9" Then
    vOut = vOut & Mid(vStr, i, 1)
  End If
Next
NumbersOnly = vOut
End Function

 

[Abhishekghorpade]

I never used functions.. Do I need to add sub and end sub to it or I can copy paste directly

 

[Rick Rothstein]

Please give me macro to delete all text from column C... I have cells with numbers that should be untouched.

Assuming the values in Column C are constants (that is, they are not formulas), give this macro a try...

Sub ClearTextCellsFromColumnC()
  Columns("C").SpecialCells(xlConstants, xlTextValues).ClearContents
End Sub

 

[Abhishekghorpade]

Yes there is no formulas in column C... There are multiple duplicates in column C.. Can macro copy unique values and paste it in new sheet1..

 

[Rick Rothstein]

Yes there is no formulas in column C... There are multiple duplicates in column C.. Can macro copy unique values and paste it in new sheet1..

How does this relate to the original question? Did the macro I posted do what you asked for?

 

[Abhishekghorpade]

How does this relate to the original question? Did the macro I posted do what you asked for?

I am sorry... Yes.. That coding is working as expected..
Coping unique values is actually one more requirement..

 

[Rick Rothstein]

I am sorry... Yes.. That coding is working as expected..
Coping unique values is actually one more requirement..

Which unique values... from the text that I removed or from the numbers that remain?

 

[Abhishekghorpade]

Which unique values... from the text that I removed or from the numbers that remain?

From the numbers that remain in column c

 

[Rick Rothstein]

From the numbers that remain in column c

I am not sure what you meant by "new Sheet1" in Message #5 , so I simply put it on Sheet1 starting at cell A1. See if this does what you want...

Sub UniqueNumbersInColumnC()
  Dim R As Long
  With CreateObject("Scripting.Dictionary")
    For R = 1 To Cells(Rows.Count, "C").End(xlUp).Row
      If Len(Cells(R, "C").Value) Then .Item(Cells(R, "C").Value) = 1
    Next
    Sheets("Sheet1").Range("A1").Resize(.Count) = Application.Transpose(.Keys)
  End With
End Sub