Dellete column F all the values that dont have 3 digits

[Excelnewbie001]

Hope someone can help I am looking for a macro to delete all the values in colum F that dont have 3 digits. here is a sample data so in this sample below 1,3, must be deleted 2,,2 must be deleted. 4,, must be deleted. So only Numbers with 3 digits must not be deleted.Thanks for any help -this must be simple for vba programmer

1, 2, 1
1, 3,
1, 3, 0
2, , 2
2, 2, 0
2, 1, 1
3, , 1
3, 1, 0
4, ,
4, , 0
4, 0, 0
, , 4
, 4, 0

 

[Excelnewbie001]

Sure

Sub Del_Values_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
  a = Range("F1", Range("F" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If a(i, 1) Like "#, #, #" Then
      k = k + 1
      b(k, 1) = a(i, 1)
    End If
  Next i
  Range("F1").Resize(UBound(b)).Value = b
End Sub

Fabulous Peter works like a charm -thank you so so so much Sir
Have a great day

 

[Excelnewbie001]

Here is another macro that you can try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Del_Values()
  Dim Addr As String
  Addr = "F1:F" & Cells(Rows.Count, "F").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(ISNUMBER(FIND("",,"",SUBSTITUTE("",""&@&"","","" "",""""))),"""",@)", "@", Addr))
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thank you Rick for posting this also works

 

[footoo]

may I please ask you just to move the data up with other words delete empty cells

Sub Foo()
Dim r$
r = Range([F1], Cells(Rows.Count, "F").End(xlUp)).Address
Range(r) = Evaluate("=IF(LEN(" & r & ")<7,""""," & r & ")")
Range(r).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End Sub

 

[Rick Rothstein]

Here is another macro that you can try...

[table="width: 500"]
[tr]
	[td]Sub Del_Values()
  Dim Addr As String
  Addr = "F1:F" & Cells(Rows.Count, "F").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(ISNUMBER(FIND("",,"",SUBSTITUTE("",""&@&"","","" "",""""))),"""",@)", "@", Addr))
End Sub[/td]
[/tr]
[/table]

Actually, the above code can be simplified slightly...

[table="width: 500"]
[tr]
	[td]Sub Del_Values()
  Dim Addr As String
  Addr = "F1:F" & Cells(Rows.Count, "F").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(ISNUMBER(FIND("", ,"","", ""&@&"","")),"""",@)", "@", Addr))
End Sub[/td]
[/tr]
[/table]

Note: I see from your last message that Peter's code worked for you which means you have single digit numbers between and next to the comma/spaces. Not that it matters to you then, but the above code will handle multi-digit numbers as well as single digit numbers.

 

[Peter_SSs]

Fabulous Peter works like a charm -thank you so so so much Sir
Have a great day

You are very welcome.

Thank you Rick for posting this also works

Does it?

Apart from the additional requirement introduced, which has not been addressed, it doesn't produce the same elimination results on the sample data as mine, so one of us must be wrong.

 

[footoo]

Does it?

Apart from the additional requirement introduced, which has not been addressed, it doesn't produce the same elimination results on the sample data as mine, so one of us must be wrong.

I'm guessing that the difference in the results relates the second piece of data posted by the OP (1, 3, )
It has CHAR(160) at the end which Rick's code does not pick-up.
(The code I posted works on the data posted - but presumably would not if there were two CHAR(160)'s at the end of the 1, 3, )

 

[Peter_SSs]

(The code I posted works on the data posted - but presumably would not if there were two CHAR(160)'s at the end of the 1, 3, )

Or
- if there were other combinations of 7 characters or more that don't fit the OP's pattern (though I suspect that is unlikely given the sample data)
- or if all the data is 7 characters or more

Your code is also relatively slow compared to mine (at least by my testing) but of course that is not relevant unless the OP's data is large and we have no information about that.

 

[oldbrewer]

and mine, with the delberate mistake removed

Is this correct ?

Sub Deletechar[/COLOR]
[COLOR=#333333]for j=1 to 1000[/COLOR]
[COLOR=#333333]if len(cells(j,1))<7 then cells(J,1)=""[/COLOR]
[COLOR=#333333]next j[/COLOR]
[COLOR=#333333]end sub[/COLOR]
[COLOR=#333333][CODE[/COLOR]

 

[Peter_SSs]

and mine, with the delberate mistake removed

It doesn't address the information provided by the OP in post 5 (& the thread title) or post 7.

 

[oldbrewer]

I put up a methodology - assuming OP had some basic knowledge - I clearly used column 1 and OP referred to 7 - if OP has zero VBA knowledge then my "approach" might be more understandable. OP will decide anyway.