Clear specific word VBA

Panoos64 · Oct 22, 2018

Hi all, kindly advise me so that to create a VBA code which should run through col. “E” and clear the specific word “Account:” but no other number or description. Therefore, that the code should run from “E2”. Below I present the original data and the expected result.

Thanking you in advance

Original data

[TABLE="class: MsoNormalTable, width: 230"]
<tbody>[TR]
[TD="width: 118"][/TD]
[TD="width: 265"]

E

[/TD]
[/TR]
[TR]
[TD="width: 118"]

1

[/TD]
[TD="width: 265"]

Account

[/TD]
[/TR]
[TR]
[TD="width: 118"]

2

[/TD]
[TD="width: 265"]

Account: 2020018

[/TD]
[/TR]
[TR]
[TD="width: 118"]

3

[/TD]
[TD="width: 265"]

Account: 2020018

[/TD]
[/TR]
</tbody>[/TABLE]

Expected result

[TABLE="class: MsoNormalTable, width: 6"]
<tbody>[TR]
[TD="width: 118"][/TD]
[TD="width: 165"]

E

[/TD]
[/TR]
[TR]
[TD="width: 118"]

1

[/TD]
[TD="width: 165"]

Account

[/TD]
[/TR]
[TR]
[TD="width: 118"]

2

[/TD]
[TD="width: 165"]

2020018

[/TD]
[/TR]
[TR]
[TD="width: 118"]

3

[/TD]
[TD="width: 165"]

2020018

[/TD]
[/TR]
</tbody>[/TABLE]

Joe4 · Oct 22, 2018

VBA shouldn't really be necessary.
Just highlight column E, use Find/Replace, replacing "Account: " with nothing, and do a Replace All
If you really want/need VBA, just turn on the Macro Recorder and record those steps above manually, and you will have the VBA code that you need.

Panoos64 · Oct 22, 2018

Yes it works so Joe, just i was thinking that a specific VBA will be more secure. I used once in another spreadsheet but it clears the letters or words from all spreadsheet

Michael M · Oct 22, 2018

If you highlight the required column only, as Joe suggested, it shouldn't affect the whole sheet !!!

Joe4 · Oct 22, 2018

...and if you use the Macro Recorder and record yourself doing it manually like I said, you will get the VBA code that you require!

That is one of the great things about the Macro Recorder; for a lot of things, you don't need to know how to write any VBA code to get the VBA code you need (or at least get you started).

Panoos64 · Oct 23, 2018

Many thanks for your advice. I thank you once again for your continuance support. Hv a great day!

My Aswer Is This · Oct 23, 2018

Try this:

Code:

Sub Replace_Me()
'Modified  10/23/2018  2:42:12 AM  EDT
Application.ScreenUpdating = False
Dim r As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "E").End(xlUp).Row
For Each r In Range("E2:E" & Lastrow)
    r.Value = Replace(r.Value, "Account: ", "")
Next
Application.ScreenUpdating = True
End Sub

JackDanIce · Oct 23, 2018

Or adjusted from recorded code without need to loop:

Code:

Sub Macro1()
    Columns("H:H").Replace What:="Account: ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
End Sub

Panoos64 · Oct 23, 2018

Thank you so much My Asw, it works perfect and according to my data. Many thanks once again for your support . Hv a great, lovely day!

Panoos64 · Oct 23, 2018

Thank you Jack, your code works too. Thanks also for your time spent for my project. Hv a nice day

Clear specific word VBA

Panoos64

Well-known Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

Panoos64

Well-known Member

Michael M

Well-known Member

Joe4

MrExcel MVP, Junior Admin

Panoos64

Well-known Member

My Aswer Is This

Well-known Member

JackDanIce

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

Clear specific word VBA

Well-known Member

Excel Facts

MrExcel MVP, Junior Admin

Well-known Member

Well-known Member

MrExcel MVP, Junior Admin

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock