macro to delete rows with certain text part 1 & copy and fill down part 1

[billnerd]

Hi All,

Part 1

I have a delimited file which is carrying 16,000 lines of data, only 7000 are relevant. I recorded a macro to clean it up but have since realised its cell referenced and I need it to delete rows which contain certain text.

In column B I have several different rows that repeat starting with for example 'year', 'date','days'. Is there a macro which could search through column b detect cells with certain text and delete the rows.

Part 2

After deleting the rows I then have to insert a new row column A with different departments in reference to sub headings in colum B. The code on column B for example is "05021 Banking" which repeats every x amount of lines and I will manually type Banking into column A and drag down until a see a new code for example'00222 Corporate'.


[TABLE="width: 145"]
<tbody>[TR]
[TD]Department[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banking[/TD]
[TD]01133 Banking[/TD]
[/TR]
[TR]
[TD]Banking[/TD]
[TD="align: right"]780115151[/TD]
[/TR]
[TR]
[TD]Banking[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banking[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banking[/TD]
[TD]**Tota[/TD]
[/TR]
[TR]
[TD]Banking[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banking[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Corporate[/TD]
[TD]05021 Corporate[/TD]
[/TR]
[TR]
[TD]Corporate[/TD]
[TD="align: right"]78075512[/TD]
[/TR]
[TR]
[TD]Corporate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Corporate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Corporate[/TD]
[TD="align: right"]78115219[/TD]
[/TR]
[TR]
[TD]Corporate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Corporate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Corporate[/TD]
[TD="align: right"]2222222[/TD]
[/TR]
[TR]
[TD]Corporate[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

Is there a macro or formula which could run down column A check numerical code first 5 digits, insert text description and fill down until finds the new code and keep repeating task until hits row 16,500. The codes do repeat and other info is also contained column B which can be ignored. At the moment I just manually input the descriptions and drag down which only takes 5-10mins but I assume there must be a better way.

Thanks

John