VBA deleting and searcing records

[eduards]

I really need some help a the moment .I am fairly new to VBA.
I have bought two books and neither have helped at all. My problem is : I am trying to write some code for excel and i have never done this before ..In my excel spreadsheet i have 4 columns .1= Site 2= Pump number 3= date and time 4= State ( Running or stopped)
The site refers to which pumping station i am dealing with , the pump no. is the actual pump ..there are up to 12 pumps at each station the date and time refers to the time a pump was started or stopped. and the state tells whether it has started or stopped. I have sorted the data according to Site , pump no and then date. The main problem is that the database has not only got the records for when the pump turned on ( running) and then when it stopped ....every so often it has a random recording that says what the state of the pumps are at that time..so i need to sort through the data and starting with a "running" value for a specific site and pump then look for the next Stopped value in the column that corresponds with the same site and pump and is sequential in date ..ie the next stop that corresponds to the Running value..I also want the code to then delete all the records(rows) (only for the same site and pump ) that are inbetween the Running and Stopped value ..these will be running state values. Then I need the code to go to the next running value after the stopped for the same site and pump and do the process again.. I need to do this for all the different sites and pumps for three years worth of data ..so as you can see it is not possible to do manually .. I would send you the code that i have tried to write over the last three days but it would only embarass me .... If anyone can help it would really be appreciated and they can reply here or to my email address...also please dont ask why i am doing this kind of coding when i am not a computer expert .. i have asked myself the same thing thousands of times.I can send the excel spreadsheet if anyone thinks they can help me.
Thanks
Eduard

 

[MortenA]

Hi eduard

The most easy thing would be a sub using relatiev references.

This example assumes 2 things:
1) cell following last cell (downwards) is empty
2) No other empty cells in D collumn bummer! must change code so that another uniqu "stop" value can be used
3) your data starts in A1, if not shift "D1" in second line appropriately

Sub Makro1()
Range("D1&quot.Select
curstate = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Do
If curstate = ActiveCell.Value Then
curstate = ActiveCell.Value
Selection.EntireRow.Delete
Else
curstate = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(curstate)
End Sub

Best Regards

Morten

 

[MortenA]

My 2) may be a little cryptic what i mean is: The cuurent axample assumes that an empty cell mean STOP. If you have empty cells in the "state collumn" then the code must be chenged to a "stop value" of you choise by testing for something else in second last line eg. Write "stop" in the row following the last row (in 4 data column only)

then loop until curstate="stop" would work just as well

Nest Regrads

Morten

 

[MortenA]

Oh an extra thing: Use a back-up!

Morten