Removing all text from a dynamic column apart from two separate defined words

[Summerso1]

Hi all,

Just learning in VBA and want to know how from a from a dynamic column meaning the length of the column can change from time to time, can I make the cells blank if they do not contain the word "Never" or "From". So it will leave all cells with these words and make the cell blank if it is any other text.

Thanks you for you time!

James

 

[Momentman]

Something like this, although you could also use an autofilter macro

Sub makeblank()
    For I = 1 To Range("A" & Rows.Count).End(xlUp).Row
        On Error Resume Next
        If InStr(1, Cells(I, "A").Value, "From") = 0 And InStr(1, Cells(I, "A").Value, "Never") = 0 Then
               Cells(I, "A").Value = ""
        End If
    Next I
End Sub

It loops from row 1 to the lastrow in the column and checks for those two words, once its not found, the cell becomes blank, the code assumes the data is in column A, change as necessary

 

[Peter_SSs]

... if they do not contain the word "Never" or "From".

Do the cells only contain these words? Or could a cell contain, for example, "Go from here to there"?

Does the column have a heading row?

 

[Summerso1]

Thank you this has worked

 

[Peter_SSs]

Thank you this has worked

That's good and you may well choose to stick with that solution. However, a few points of clarification & questions.

1. The solution looks for either of the two words anywhere in the cell rather than being the only word in the cell. If your cells only contain "Never" or "From" the code will still work, but there would be simpler, more efficient code to do that. Hence my previous questions, which I'd still be interested to know the answers to.

Do the cells only contain these words? Or could a cell contain, for example, "Go from here to there"?

Does the column have a heading row?

2.The solution is case sensitive. That is, if the cell contained "Go from here to there" it would be cleared because it contains "from" not "From".
a) Is that possible with your data?
b) Is that what you want?

3. Looping through worksheet rows one at a time is not always the most efficient way to process data. If you don't have many rows, it may not be an issue but if your data is reasonable large it may be better to look at alternative ways.
a) Approximately how many rows of data are you likely to be dealing with?
b) Approximately what percentage of those rows are likely to need clearing?