Delete row from Excel depending on another cell

[Epsilon101]

Hi,

This is in Excel.
I have got some rows of transactions.
There is an ID number for the accounts in column A and a code for the transaction type in column C.

I am trying to do something in vba or with formulae, but its proving difficult.

The idea is to check column C, if the transaction type = 'OUT'. Get the Account ID in column A and delete all rows with that account ID.

Something like below for deleting the row, just not A:A.

Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

I was thinking maybe using an array to hold the account ID's and loop through each section in the array and deleting the rows that match.

Array would have to be the same length as the amount of rows.

Im not exactly sure how to accomplish this and there is probably a better way to do it.

Any help would be appreciated

Thanks


---------------------------------------

Neil

 

[KenWright]

If this is not necessarily to be part of a VBA routine then why not just select all your data, do data / Filter / Autofilter, filetr Col C on OUT, Col A on your ID, select all the visible cells in any of the columns (Excluding headers of course), do Edit / Go To / Special cells / Blanks, then Edit / Delete / Entire Row.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Epsilon101]

Sorry my mistake i used the wrong bit of code above.

I dont want to delete blanks mainly cos i dont have any.

Ive got 9000 rows , but easiest way to explain would be

1 account with 10 transactions on it

So 10 rows the accountID is the same on each row, but the trans code can change.

If a row has a trans code of 'OUT' i need to get the accountID and then delete all rows with the same accountID.

So all 10 rows will be deleted because of that 1 'OUT' code.



---------------------------------------

Neil

 

[Epsilon101]

Ah nvm, done it.

Autofilter on 'OUT' selected the accountID's.

Did a vlookup check formula on the results and kept any that gave an error value.

Thanks for your previous suggestion that helped.

---------------------------------------

Neil

 

[KenWright]

My pleasure

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------