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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
 
Upvote 0
... 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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top