Clear columns data

Panoos64 · Jan 21, 2018

Hi all, I would be greatly appreciate to provide me support for a VBA code so that to run through row “1” and where find the words “Count” & “Total” should clear entire columns' data. Note that on left of the above words contains numbers and my data start from “A1”. In F.1. are the original data and in F.2. is the expected result. Many thanks in advance

F.1.

Department	1001	1001	1001	1001	1001 Count	1001 Total	1007	1007	1007	1007	1007 Count	1007 Total
Item 100105	50		150	600	3	800	55	63	408	478	4	1,004
Item 100113	200	400	600	2,400	4	3,600	205	555	978	845	4	2,583
Item 100118	100		300	1,200	3	1,600	150	205	963	904	4	2,222
Item 100122	150		450	1,800	3	2,400	355			851	2	1,206
Item 100141	75	150	225		3	450	79	168	645	547	4	1,439

F.2.

Department	1001	1001	1001	1001	1007	1007	1007	1007
Item 100105	50		150	600	55	63	408	478
Item 100113	200	400	600	2,400	205	555	978	845
Item 100118	100		300	1,200	150	205	963	904
Item 100122	150		450	1,800	355			851
Item 100141	75	150	225		79	168	645	547

AlphaFrog · Jan 21, 2018

Code:

[color=darkblue]Sub[/color] Clear_Count_Total_Columns()
    [color=darkblue]Dim[/color] cell [color=darkblue]As[/color] Range
    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Range("A1", Cells(1, Columns.Count).End(xlToLeft))
        [color=darkblue]If[/color] cell.Value [color=darkblue]Like[/color] "*Count" [color=darkblue]Or[/color] cell.Value [color=darkblue]Like[/color] "*Total" [color=darkblue]Then[/color] cell.EntireColumn.ClearContents
    [color=darkblue]Next[/color]
    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Rick Rothstein · Jan 21, 2018

Here is another macro (a one-liner) that you can also consider...

Code:

[table="width: 500"]
[tr]
	[td]Sub Clear_Count_Total_Columns()
  Range("A1").CurrentRegion = Evaluate(Replace("IF(ISNUMBER(Search(""Count"",(1:1 @)))+ISNUMBER(Search(""Total"",(1:1 @))),"""",IF(@="""","""",@))", "@", Range("A1").CurrentRegion.Address))
End Sub[/td]
[/tr]
[/table]

Panoos64 · Jan 21, 2018

Hi Alpha, it works perfect. Thanks for your support and your time spent for my project. Have a great day!

Panoos64 · Jan 21, 2018

Hi Rick, your code works nicely. Many thanks for your continuous support. Have a great, lovely day!

Clear columns data

Panoos64

Well-known Member

Excel Facts

AlphaFrog

MrExcel MVP

Rick Rothstein

MrExcel MVP

Panoos64

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

Clear columns data

Panoos64

Well-known Member

Excel Facts

AlphaFrog

MrExcel MVP

Rick Rothstein

MrExcel MVP

Panoos64

Well-known Member

Panoos64

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