Delete rows based on condition vba

[coolkiran]

Hi All

I have a excel sheet which has 4 column, i would like to delete rows based on some criteria's.

My columns are Record #, Amount, Note 1 and Note 2.

My Criteria will be : Some time Note 1 or Note 2 will have record #.

I have attached example file, in that first row having some note 1, which is a record # we can find it in Row no : 22.

In this case sum of these amount is Zero, so i need to delete both rows. Similarly i need to check all rows which has Note 1 and Note 2 against Record # and Amount,

 

[NARAYANK991]

Hi ,

So based on the data in your uploaded file , which rows will be deleted , and which ones will remain at the end ?

Narayan

 

[coolkiran]

Hi ,

So based on the data in your uploaded file , which rows will be deleted , and which ones will remain at the end ?

Narayan

As per my file, all rows will get delete. because all are matching

 

[Marc L]

Hi,

ever not need a VBA code, easy to identify via a beginner level formula !

So many ways, best depends on the number of rows of real worksheet
and its real layout …
​

 

[coolkiran]

Thanks for your reply, i need it in macro because i need to call the macro function from another application.

Regarding Layout the header will not change And row may vary.

 

[Marc L]

i need to call the macro function from another application

Another application : which one ?‼
Better is to create a code within this application.

A must see : Find method in VBA inner help …
​

 

[NARAYANK991]

Hi ,

See if this is OK. Please backup your working file before you run the macro , since no undo is possible once the macro is run.

Narayan

 

[coolkiran]

Hi ,

See if this is OK. Please backup your working file before you run the macro , since no undo is possible once the macro is run.

Narayan

Thanks for the code Narayan.

Small issue is if i changed any value in Amount, Eg: i changed value as 4903.39 in row number 2, when i run macro, it shouldn't delete, because its doesn't have matching opposite.

But current macro deletes it.

 

[NARAYANK991]

Hi ,

I just downloaded the file from this thread , changed the amount from 4902.39 to 4903.39 , and ran the macro.

After completion , this row remained undeleted.

Narayan

 

[coolkiran]

Hi ,

I just downloaded the file from this thread , changed the amount from 4902.39 to 4903.39 , and ran the macro.

After completion , this row remained undeleted.

Narayan

Correct, but it shouldn't delete 4902.39 (Row No 22) as well. it doesn't have opposite, if we changed row no 2 as 4903.39

 

[NARAYANK991]

Hi ,

Sorry , but I did not check by changing both instances ; changing the later instance of -4902.39 to -4903.39 created a problem.

I have revised the code in the attached file.

Narayan

 

[NARAYANK991]

Hi ,

Give me some time to revise the code to take this into account.

Narayan

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[coolkiran]

Hi ,

See if this is OK.

Narayan

Its working perfectly. Thanks a lot for the code.