PDA

View Full Version : [SOLVED] if find less that six numeric value clean the cell



parscon
09-29-2014, 02:15 AM
I need a VBA Code that check column D and if find less than six number value (six numeric value with out space) clean the data on that cell . For example

In Column D Cell 1 i have these data :
D1 :#S/N - 12862,S/N - 11546,SER NO 4890 -,SER NO 1706 -@ SER NO 1709 - (USA),SER NO

In Column D Cell 2 I have these data
D2 : SER NO 3064) -,SER NO 10765 -,SER NO 2597 -117367#

When run the VBA it will clean data on Column D Cell 1 Because cannot find six numeric value . but in D2 there is one 117367 and it will not deleted .

For example if i have data like this : 12345 6 or 1234-56 or 123456- also the cell will be clean numeric value must be without space or any special character .

jonh
09-29-2014, 04:53 AM
Public Sub Clean()
For Each c In ActiveSheet.Range("d:d").Cells
If c.Text = "" Then Exit For
If Not chkNum(c.Text, 6) Then c.Formula = ""
Next
End Sub

Private Function chkNum(s As String, concchar As Byte) As Boolean
Dim n As Integer
For i = 1 To Len(s)
Select Case Asc(Mid(s, i, 1))
Case 48 To 57
n = n + 1
If n = concchar Then Exit For
Case Else: n = 0
End Select
Next
chkNum = n >= concchar
End Function

parscon
09-29-2014, 05:09 AM
Thank you very much and my request its done.