Deleting cells if there is an alphabetical character in it

N

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.
 
B

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
 
M

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
 
N

N1KO

Thanks, both answers worked perfectly.

Mike H said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top