Solved: clear contents based on conditions

[rafi_07max]

I would like to clear the cell contents if it doesn’t contain any number (0, 1, 2…9) or letter (a, b, c…z)

The codes below did worked for me, but the codes are space sensitive.

What I mean is that if one of my cell content is something like “???”(note that there is no space in between) then the cell content will be cleared.

But if the cell content is something like “?? ?” (There is a space in between) then the cell content is not cleared.
But actually i want this to be cleared regardless of whether ther's a space or not because ther the cell don't have any letter or number.


How can I solve this problem?


[vba]
Private Sub CommandButton1_Click()
Dim Rng As Range, MyCell As Range, i As Long
Set Rng = ActiveSheet.Range("A1" & ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
For i = 1 To Len(MyCell.Text)
HasNum = IsNumeric(Mid(MyCell, i, 1))
If HasNum = True Then
GoTo Nxt
End If
If Mid(MyCell.Value, i, 1) Like "[A-Z a-z]" Then
GoTo Nxt
End If
Next i
MyCell.ClearContents
Nxt:
Next MyCell
End Sub
[/vba]

[GTO]

Greetings,

Just a comment as I cannot see your data. The way you are finding the last row would mean that there must be data in Col D as far down as the data in any column would be. For instance, if the last val in Col D was in Row 20, but Col B had a val in Row 21, this would be missed. Not sure if that is a concern, just thought to mention in case...

Anyways, I think a simple pattern would work:

[vba]Option Explicit

Sub exa()
Dim Rng As Range, MyCell As Range, i As Long
Static REX As Object

Set Rng = ActiveSheet.Range("A1" & ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row)

If REX Is Nothing Then Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = False
.IgnoreCase = True
.Pattern = "[A-z0-9]"

For Each MyCell In Rng
If Not .Test(MyCell.Value) Then
MyCell.ClearContents
End If
Next
End With
End Sub[/vba]
Hope that helps,

Mark

[rafi_07max]

Thanks GTO for your help, it worked.

And also also for pointing out the mistake in my program, ya it is a concern for me, i will take a look at it.
Thank you.