Clearing Specific Text

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,998
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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,998
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
51,145
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
51,145
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,998
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
51,145
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,998
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
 

Forum statistics

Threads
1,144,278
Messages
5,723,465
Members
422,498
Latest member
KAT112014

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
Top