Clearing Specific Text

[howard]

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

 

[venkat1926]

try this

Sub test()

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

End Sub

 

[howard]

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]

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]

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]

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]

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]

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