Delete Rows where cell = "Settled"

[adam2308]

I want to add some code to an ActiveX Control button that deletes any rows in the active sheet where column BA of that row has the word "Settled" in there.

For example cells BA4 and BA6 has the word "Settled" in there all the other rows don't. So, when I press this button I want it to delete only rows 4 and 6.

Could anyone please help?

Thanks.

[Richard Buttrey]

Hi,

Get the macro to autofilter the rows for the word 'settled' and then once filtered have the macro delete all the visible rows with a:

[your filtered range].SpecialCells(xlCellTypeVisible).EntireRow.Delete

instruction.

[patel45]

Please Login or Register  to view this content.

[adam2308]

Thanks for your replies.

Richard - I don't quite follow what I need to do with your method.

Patel45 - When I first looked at your code I thought it was exactly what I was looking for but when I applied it to my command button it doesn't seem to do anything.

I've attached a workbook as an example to hopefully make it more explanatory.

[mike7952]

Give this a try

Please Login or Register  to view this content.

[adam2308]

Mike - That works perfectly!

Cheers guys!

[HaHoBe]

Hi, adam2308,

as BA is only a formula and the values houses in AY why not try this one:

Please Login or Register  to view this content.

Ciao,
Holger