D have a spreadsheet and I want to clear all cells in Column B that is not
equal to Auto. Any help is certainly appreciated. Thanks

Sub ClearAuto()
'Clear all cells with the value "Auto"
Dim i as Integer
Dim ItemCount as Integer
ItemCount = Application.WorksheetFunction.CountA(Columns("B"))
Range("B1").Select
For i = 1 to ItemCount
If ActiveCell.Value = "Auto" then ActiveCell.ClearContents
ActiveCell.Offset(1,0).Select
Next i
End Sub

"JOUIOUI" wrote:

D have a spreadsheet and I want to clear all cells in Column B that is not
equal to Auto. Any help is certainly appreciated. Thanks

Hi Paul, thanks for responding. I actually need to clear every cell that
does not include the text auto....I need to keep auto and clear any other
data in any of the other cells in that column. Can you help with that.
Thanks a bunch


"Paul Mathews" wrote:

Sub ClearAuto()
'Clear all cells with the value "Auto"
Dim i as Integer
Dim ItemCount as Integer
ItemCount = Application.WorksheetFunction.CountA(Columns("B"))
Range("B1").Select
For i = 1 to ItemCount
If ActiveCell.Value = "Auto" then ActiveCell.ClearContents
ActiveCell.Offset(1,0).Select
Next i
End Sub

"JOUIOUI" wrote:

D have a spreadsheet and I want to clear all cells in Column B that is not
equal to Auto. Any help is certainly appreciated. Thanks

Whoops, sorry, I obviously did a dyslexic thing there. All you need to do is
change the conditional expression in the code I sent:

Change the item "If ActiveCell.Value = "Auto" to "If ActiveCell.Value <
"Auto"

That should get you the result you need. Sorry about that.

"JOUIOUI" wrote:

Hi Paul, thanks for responding. I actually need to clear every cell that
does not include the text auto....I need to keep auto and clear any other
data in any of the other cells in that column. Can you help with that.
Thanks a bunch


"Paul Mathews" wrote:

Sub ClearAuto()
'Clear all cells with the value "Auto"
Dim i as Integer
Dim ItemCount as Integer
ItemCount = Application.WorksheetFunction.CountA(Columns("B"))
Range("B1").Select
For i = 1 to ItemCount
If ActiveCell.Value = "Auto" then ActiveCell.ClearContents
ActiveCell.Offset(1,0).Select
Next i
End Sub

"JOUIOUI" wrote:

D have a spreadsheet and I want to clear all cells in Column B that is not
equal to Auto. Any help is certainly appreciated. Thanks

Hi again,

I forgot to mention that the code I sent presumes that you don't already
have any blank cells in your list of items. If you may possibly have blanks
in that list, you might be best off providing a range name for the data list.
Let's say you call the list "DataList". Then modify the code as follows:

Sub ClearNonAuto()
'Clear all cells with a value other than "Auto"
Dim i as Long
Dim ItemCount as Long
ItemCount = Range("DataList").Rows.Count
Range("DataList").Cells(1,1).Select
For i = 1 to ItemCount
If ActiveCell.Value < "Auto" then ActiveCell.ClearContents
ActiveCell.Offset(1,0).Select
Next i
End Sub


"JOUIOUI" wrote:

Hi Paul, thanks for responding. I actually need to clear every cell that
does not include the text auto....I need to keep auto and clear any other
data in any of the other cells in that column. Can you help with that.
Thanks a bunch


"Paul Mathews" wrote:

Sub ClearAuto()
'Clear all cells with the value "Auto"
Dim i as Integer
Dim ItemCount as Integer
ItemCount = Application.WorksheetFunction.CountA(Columns("B"))
Range("B1").Select
For i = 1 to ItemCount
If ActiveCell.Value = "Auto" then ActiveCell.ClearContents
ActiveCell.Offset(1,0).Select
Next i
End Sub

"JOUIOUI" wrote:

D have a spreadsheet and I want to clear all cells in Column B that is not
equal to Auto. Any help is certainly appreciated. Thanks

Great Paul, thanks so much. I'm really starting to understand this. Thanks
for the explanation, it helps a bunch!

"Paul Mathews" wrote:

Hi again,

I forgot to mention that the code I sent presumes that you don't already
have any blank cells in your list of items. If you may possibly have blanks
in that list, you might be best off providing a range name for the data list.
Let's say you call the list "DataList". Then modify the code as follows:

Sub ClearNonAuto()
'Clear all cells with a value other than "Auto"
Dim i as Long
Dim ItemCount as Long
ItemCount = Range("DataList").Rows.Count
Range("DataList").Cells(1,1).Select
For i = 1 to ItemCount
If ActiveCell.Value < "Auto" then ActiveCell.ClearContents
ActiveCell.Offset(1,0).Select
Next i
End Sub


"JOUIOUI" wrote:

Hi Paul, thanks for responding. I actually need to clear every cell that
does not include the text auto....I need to keep auto and clear any other
data in any of the other cells in that column. Can you help with that.
Thanks a bunch


"Paul Mathews" wrote:

Sub ClearAuto()
'Clear all cells with the value "Auto"
Dim i as Integer
Dim ItemCount as Integer
ItemCount = Application.WorksheetFunction.CountA(Columns("B"))
Range("B1").Select
For i = 1 to ItemCount
If ActiveCell.Value = "Auto" then ActiveCell.ClearContents
ActiveCell.Offset(1,0).Select
Next i
End Sub

"JOUIOUI" wrote:

D have a spreadsheet and I want to clear all cells in Column B that is not
equal to Auto. Any help is certainly appreciated. Thanks