Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting cells if there is an alphabetical character in it
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting cells if there is an alphabetical character in it
N1KO,
Try deleting any cell with a string value in it: Sheets("Town Pubs Cross Ref report").Range("B3:B21568").SpecialCells(xlCellTyp eConstants, 2).ClearContents HTH, Bernie MS Excel MVP "N1KO" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting cells if there is an alphabetical character 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting cells if there is an alphabetical character 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting the ' character | Excel Discussion (Misc queries) | |||
Deleting first character issue | New Users to Excel | |||
Userform: looking for and deleting character | Excel Programming | |||
Deleting the same character automatically in each cell | Excel Worksheet Functions | |||
Deleting the leading character in a series of cells | Excel Programming |