filtering/deleting rows

[bradyj7]

Hi there,

I have large sheets with time in column c and another variable, electricity being consumed in column d. In column d there are tens of rows of concentive zeros when people are not consuming electricty then when they plug in an item positive values occur. So there will be rows of zeros then rows of positive values then rows of zero agian when they plug out etc. However I am only interest in what time the person plugs in the item. So I only want to keep the row of the first positive value that occurs in each electricity consuming period.

Could some one help me out with a macro? I hope you can understand that.

thank you

 

[AlphaFrog]

Put this formula in E1
=D1>0

Put this formula in cell E2 (or any empty column) and copy it down the column for each cell in column D
=AND(D1=0,D2>0)

The formula will return TRUE only for the 1st positive values in column D after each zero.

Select column E
Select from the menu; Data\ Filter\ Autofilter
That will put a drop-down list in cell E1
Select from the E1 drop-down list "TRUE" to show only the rows with TRUE

You can then copy and paste the visible rows to a new sheet or do what you want to them.