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

Summerso1

New Member
Joined
Jul 24, 2014
Messages
45
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

Well-known Member
Joined
Jan 11, 2012
Messages
3,963
Something like this, although you could also use an autofilter macro

Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,230
Office Version
365
Platform
Windows
... 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?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,230
Office Version
365
Platform
Windows
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?
 

Forum statistics

Threads
1,081,747
Messages
5,361,038
Members
400,610
Latest member
ebey

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top