Search column for value that's not zero, and delete rows except before and after value.

[johnstevens]

This one I'm having a hard time figuring out....and explaining. It will be easier to understand if you first look at the attached picture.

Example.jpg

I want to search the H column for values that are not zero and basically select everything else except 1 row before and after each value that isn't zero, then delete the other rows. I basically want to create a bit of a buffer between the non-zero numbers. It is a bit tricky because as you can see in the picture there can be two value in the H column one after the other. There will never be more than 2 values in a row in the H column. I have 270,000 rows across multiple tabs to go through, so getting this to work would be nice.

Thanks to anyone in advance for lending a hand!

[johnstevens]

I know it's not exactly the easiest thing to do, but does anyone have an idea of how to do this?

[Bernie Deitrick]

Run this macro, which will work on whatever sheet is active - you can also loop for all the sheets if you want to, left as your homework

Please Login or Register  to view this content.

[johnstevens]

That works like a charm Bernie. Thank you for taking the time to figure that one out for me. You got some serious skill there. It will help save hours of time.

Thanks again!

[Bernie Deitrick]

It will help save hours of time.

That's why I love macros!