There is a column C and in it from the 3rd to the 25th row the words are already inscribed - Dear, Meal, Run. How to write a macro that will delete other words, that is, if the column contains (manually enter some other word or number (not important)) then excel will simply delete this value. The words in this column should only be these for other clearcontent code that does not work (

Sub dd() For i = 1 To 20 If Cells(i, 3).Value <> "Deal" Or "Meal" Or "Run" Then Cells(i, 20).ClearContents End If Next i End Sub
  • Refine the task. In column 3, delete words that are not equal to the three shown. That is, delete all that were not included in the list ... And then write: delete only words that you specified manually ... In the cells, one or more words? Column 20 - what to do with it? Or search in the third, delete in 20, as in the code? In general, please specify. - vikttur
  • @vikttur "In column 3, delete words that are not equal to the three indicated" - yes, "did not make the list ..." did not fall into the interval (1 to 20) "In the cells, one word or several?" - one by one - trabl
  • >> column C and in it from 3 to 25 row. In the comments - 1-20. Where is the truth? - vikttur

1 answer 1

Invalid validation syntax. Right:

 If Cells(i, 3).Value <> "Deal" Or Cells(i, 3).Value <> "Meal" Or Cells(i, 3).Value <> "Run" Then

So correct in terms of syntax, but incorrectly logically. The Or operator is used. With such a record can not be the result False .

Examples

In the cell, the word " Deal ":

 "Deal" <> "Deal" Or "Deal" <> "Meal" = False + True = True

In the cell " Excel ":

 "Excel" <> "Deal" Or "Excel" <> "Meal" = True + True = True

In this case, you need to use the operator And ( And ).

===========================

We look through lines 3 through 25. If the value in the cell of column C is not equal to the specified one, we clear the cell. Instead of And , the If cascade allows you to cut off extra calculations.

 Sub DelVal1() Dim i As Long For i = 3 To 25' цикл по строкам If Cells(i, 3).Value <> "Deal" Then If Cells(i, 3).Value <> "Meal" Then If Cells(i, 3).Value <> "Run" Then Cells(i, 3).ClearContents End If End If Next i End Sub

The cell value can be searched for in a text composed of the search words:

 Sub DelVal2() Dim i As Long For i = 3 To 25 If Not ("DealMealRun" Like "*" & Cells(i, 3).Value & "*") Then _ Cells(i, 3).ClearContents Next i End Sub

If there are many words, it makes sense to add them to the array:

 Sub DelVal3() Dim ArrVal() Dim i As Long, j As Long ArrVal = Array("Deal", "Meal", "Run") ' значения в массив For i = 3 To 25 ' цикл по строкам For j = 0 To UBound(ArrVal) ' цикл по массиву If Cells(i, 3).Value = ArrVal(j) Then Exit For Next j ' ни одно не найдено - очищаем ячейку If j = UBound(ArrVal) + 1 Then Cells(i, 3).ClearContents Next i End Sub