Deleting cells if there is an alphabetical character in it

[N1KO]

Here is some code i've used to copy values from cell to cell and i now want
to make all the cells with letters (a, b, c, etc) in them blank. For the life
of me i can't make any that actually start with a number become blank.

Help would be appreciated.

Sub Codes()

Sheets("Town Pubs Cross Ref report").Activate
Range("B3").Select
Do
If ActiveCell.Value = "" Then
ActiveCell.Value = ActiveCell.Offset(0, -1)
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row = 21568
End Sub

Sub Delete()

Sheets("Town Pubs Cross Ref report").Activate
Range("B3").Select
Do
If ActiveCell.Value <> "?????" Then
ActiveCell.Value = ""
Cancel = True
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row = 21568
End Sub

So basically i want to clear any cell in column B that has a letter in it
regardless whether it has a number in it.

 

[Bernie Deitrick]

N1KO,

Try deleting any cell with a string value in it:

Sheets("Town Pubs Cross Ref report").Range("B3:B21568").SpecialCells(xlCellTypeConstants,
2).ClearContents


HTH,
Bernie
MS Excel MVP

 

[Mike H]

Hi,

Try this

Sub Delete()
Sheets("Town Pubs Cross Ref report").Activate
Range("B3").Select
Do
If Not IsNumeric(ActiveCell.Value) Then
ActiveCell.ClearContents
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row = 21568
End Sub


Mike

 

[N1KO]

Thanks, both answers worked perfectly.

Hi,

Try this

Sub Delete()
Sheets("Town Pubs Cross Ref report").Activate
Range("B3").Select
Do
If Not IsNumeric(ActiveCell.Value) Then
ActiveCell.ClearContents
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row = 21568
End Sub


Mike