Macro to delete rows where positive Value is same as negative value in Specific Column

[Howardc1001]

I have a spreadsheet with several columns


I need a macro to delete all rows in Col with Header "Bank" in row 1 where the values are positive and equal eg 50 -50 , 150, -150 etc


I have attached sample data and highlighted the items in yellow where the rows must be deleted



It would be appreciated if someone could kindly assist me

[sintek]

One way...

Please Login or Register  to view this content.

[Howardc1001]

Thanks for the help. The code works perfectly

I just need one small change. The hander "Bank" is not always in Col B


The rows to be deleted where the values are positive and equal eg 50 -50 , 150, -150 etc , will always be in the Column where the header is "Bank" which is in row 1


It would be appreciated if you could kindly amend your code to accommodate this

[sintek]

Need to learn my friend...Make use of snippets here

Please Login or Register  to view this content.

[Howardc1001]

Thanks for the updated code. Will go through the link to previous code per Snippets Link so that I understand and learn

[Howardc1001]

Hi Sintek


the code works perfectly and runs quickly on my same data. However it is very slow when run using real data where the number of rows is +- 600,000


I have set the code to change calculation to manual, but still take +- 20 mins to run

it wold be appreciated if you could amend your code to run faster


I have the autofilter on. Do I first need to set the autofilter off before running the macro ?


your assistance is most appreciated

[sintek]

Upload a sample file with a bigger data set

[Howardc1001]

See attached sample data


Kindly test and amend code so that I runs faster

[sintek]

See if this helps...

Please Login or Register  to view this content.

EDIT....Just noticed the above wont work...There are multiple Pos & Neg values...

[Howardc1001]

Thanks for your effort

[sintek]

Give this a go...
Not sure if there is a quicker solution...
Lets see if anyone else comes up with another solution...

Please Login or Register  to view this content.

[Howardc1001]

Thanks foe updated code


when running macro, it comes up with message (not responding) at end of workbook name


not sure what would cause that. I have office 2016 (64 Bit)

[sintek]

When stepping through the code in Post 11 it takes 2 seconds...
Running the code hangs....This is a very strange phenomenon...

Anyone have any ideas...

[jindon]

Please Login or Register  to view this content.

[Howardc1001]

Hi Jindon


Thanks for the help. Your code works perfectly and completes task in seconds


Howard

[Howardc1001]

Hi Sintek


I ran your code on my Laptop. It took +- 10 mins to run. Possibly my hardware settings needs to change to take full advantage of my ram which is 8mb


I am using office 2016 (64 bit)


Not sure how to change these to increase speed and take full advantage of Ram

[sintek]

Yeah, jindon's code always rocks...Still would like to know why when stepping through my code it takes 1.5 seconds but running it ... hangs...