rafi_07max
11-25-2010, 11:28 PM
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?
Private Sub CommandButton1_Click()
Dim Rng As Range, MyCell As Range, i As Long
Set Rng = ActiveSheet.Range("A1:D" & 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
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?
Private Sub CommandButton1_Click()
Dim Rng As Range, MyCell As Range, i As Long
Set Rng = ActiveSheet.Range("A1:D" & 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