Macro to find a column header and delete certain rows of data

[robertguy]

Hi,


Can any please advise me of the macro code needed to search a worksheet for a heading in row 1 called ‘Contract Type’ (if it exists) and delete any rows where there is the text ‘Fixed Price’ (if any exists) in any of the cells in that column

Any assistance would be greatly appreciated

Many thanks

Rob

N.B Excel version 2007

[Alf]

Perhaps a macro like this?

Please Login or Register  to view this content.

Alf

[robertguy]

Alf,

just what the Doctor ordered !! It worked a treat.

Many thanks indeed

Regards


Rob

[Alf]

Glad to be of help. Thanks for feedback and rep!

Alf

[Alf]

Hi Rob

I missed the obvious i.e. if no cell in row 1 has the text "Contract Type" then the macro throws an error and stops. Solution to that is to place auto filter settings "inside" the If statement. I'm also not totally happy with the CountA command this assumes every cell in range is not empty. So with all that in mind I've written a more "robust" macro.

This finds the rightmost column that is not "empty" and checks all columns (row 1) from first to the last column and I've also put the auto filter settings "inside" the If test.

Please Login or Register  to view this content.

Sorry about this.

Alf

[robertguy]

Alf,

I've just picked up your updated thread and have amended by code accordingly

Many thanks Again


Rob