Thread: clear cell contents based on conditions

I would like to clear any cell that only contains “?” and/or “,”.
Take a look at the image below:
1.JPG

From the image above we can see that in cell D4, E1, F2, F3 and F4 the contents consist of “?” and/or “,”, so I want my macro to detect such cells and clear its content.

Do not clear contents of cell that has together with “?” and/or “s” there may be other character. For e.g. from the above image if you look at cell D2 there is other words such as jp6_7, jp6_8, so such cells should not be cleared

I would like to do this to the entire column D, E and F

The final outcome should look like the image below
2.JPG

I have also attached a sample worksheet,
Book2.xls
Sheet 1 shows the initial texts
Sheet 2 contains a button where the macro should be written
Sheet 3 shows how the final outcome looks like

Why not just use the Find & Replace tool found in the EDIT dropdown of the toolbar, search for ? and replace with nothing.

Are there always numbers contained in cells that also contain ? that you want to keep?

Well as long as the cells that you want to keep will ALWAYS have at least one number in it this should work

'code supplied by Simon Lloyd
'16/11/2010
'Microsoft Office Help
'Excel Conference Poll
Sub clear_Cells()
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
    Next i
MyCell.ClearContents
Nxt:
Next MyCell
End Sub

Not tested as i dont have time

'Code supplied by Simon Lloyd
'17/11/2010
'Microsoft Office Help
'Excel Conference Poll
Sub clear_Cells()
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 Not Mid(mycell.Value, intI, 1) Like "[A-Z,a-z,0-9,.]" then goto Nxt
            If HasNum = True and If Not IsNumeric(mycell)  Then
               GoTo Nxt
            End If
    Next i
MyCell.ClearContents
Nxt:
Next MyCell
End Sub

Like i said, i didnt have time to test it, thsi works or seems to work for me:

'Code supplied by Simon Lloyd
'18/11/2010
'Microsoft Office Help
'ExcelConference Poll
Sub clear_Cells()
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