Clearing Specific Text

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,652
Office Version
  1. 2019
Platform
  1. Windows
I have a spreadheet that has the following text appearing in column A

1) AVIS plus additional text for eg AVIS0106 -this changes every month,
but will allways contain the word Avis
2) VALUE plus additional text for eg VALUE0106-this changes every month,
but will allways contain the word Avis

I need VBA code that will clear the above text, plus any data to the right of this text in column B:J

Your assistance will be most appreciated

Regards

Howard
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
try this

Code:
Sub test()

Columns("B:J").Replace what:="avis*", replacement:=""
Columns("B:J").Replace what:="value*", replacement:=""

End Sub
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,652
Office Version
  1. 2019
Platform
  1. Windows
Hi Venkat

I need AVIS.* i.e AVIS + Text after AVIS for EG AVIS0106 as well as VALUE + Text after Value for eg VALUE0106 cleared as well as data in line with AVIS and VALUE in columns B:J AVIS & VALUE appear in column A

Regards

Howard
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,773
Office Version
  1. 365
Platform
  1. Windows
howard

Is this it?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ClearAVISandVALUE()
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    LastRow = Range("A65536").End(xlUp).Row
    <SPAN style="color:#00007F">For</SPAN> r = 1 <SPAN style="color:#00007F">To</SPAN> LastRow
        <SPAN style="color:#00007F">If</SPAN> InStr(Cells(r, 1).Value, "AVIS") <SPAN style="color:#00007F">Or</SPAN> InStr(Cells(r, 1).Value, "VALUE") <SPAN style="color:#00007F">Then</SPAN>
            Range(Cells(r, 1), Cells(r, 10)).ClearContents
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> r
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Edit: Have added two extra lines to increase speed if there are many rows.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,773
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This should be a lot faster. Assumes that the AVIS or VALUE text does not appear in row 1. Could easily be modified if this assumption is incorrect.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ClearAVISandVALUE()
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">With</SPAN> Columns("A:J")
        .AutoFilter Field:=1, Criteria1:="=AVIS*", Operator:=xlOr, _
        Criteria2:="=VALUE*"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Range("A3:J65536").ClearContents
    ActiveSheet.AutoFilterMode = <SPAN style="color:#00007F">False</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,652
Office Version
  1. 2019
Platform
  1. Windows
Hi Peter

Thanks for the help.

Have tested the code, works perfectly.

Are there any VBA books, CD's or online VBA tutorials that you can recommend.

I'm still a VBA novice and would like to learn more on how to program using VBA.

Regards

Howard
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,773
Office Version
  1. 365
Platform
  1. Windows
Hi Peter

Thanks for the help.

Have tested the code, works perfectly.

Are there any VBA books, CD's or online VBA tutorials that you can recommend.

I'm still a VBA novice and would like to learn more on how to program using VBA.

Regards

Howard
I still class myself in the novice category and have progressed to where I am largely by studying posts on this board, using the built-in VBA Help and just trial and error. No books or formal training - probably why much of my code is poor. But I take note of suggestions made by those on this board who obviously are far more advanced than me and I believe that I am (slowly) improving.
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,652
Office Version
  1. 2019
Platform
  1. Windows
Steve

Your knowledge is much better than mine. I must admit that mine is slowly improving since posting questions on the board. There are some real pro's out there.

Regards


Howard
 

Watch MrExcel Video

Forum statistics

Threads
1,111,596
Messages
5,541,154
Members
410,543
Latest member
ExcelGlenn
Top