×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBA deleting and searcing records

VBA deleting and searcing records

VBA deleting and searcing records

(OP)
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

RE: VBA deleting and searcing records

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").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



RE: VBA deleting and searcing records

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

RE: VBA deleting and searcing records

Oh an extra thing: Use a back-up!

Morten

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close