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.

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


"N1KO" <(E-Mail Removed)> wrote in message
news:3D699D8F-AFDE-4994-A42E-(E-Mail Removed)...
> 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.

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" wrote:

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

Thanks, both answers worked perfectly.

"Mike H" wrote:

> 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" wrote:
>
> > 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.