Add Row On Unmatched Condition

  • I have a summary list of all projects that my employees work on over the course of the month. Its gets sorted by date and project and is a pretty extensive list.


    My goal is simple...to search through the list and add a blank row between each project.


    I have written this code, that does work, but it takes quite some time to execute and I know there has to be a better way.


    Can you guys take a look at it and see if there is more efficient way to do this?



    Thanks in advance!!

  • Re: Add Row On Unmatched Condition


    I just benchmarked your code against the following code:



    They both took exactly the same time (0.0164 seconds) to process a 100 row sample. I don't have any ideas on how to better this procedure.


    How many rows of data do you have and how long does it take to process?

  • Re: Add Row On Unmatched Condition


    Hi,


    I started coding this, but dont have anything to test it on.


    Hopefully you get the idea.


  • Re: Add Row On Unmatched Condition


    Hi Reafidy,


    I ran your code on my sample and it took 0.0318 seconds - Almost double the time. LOL, I can't believe i'm benchmarking these. We're really splitting hairs here aren't we : D
    You'd also need to stop it from inserting a row above the first project which I'm sure you could do.


    Edit: Just redid the bench mark and it took the same time as the others. I must have had another program chewing up the juice while i did the first test.

  • Re: Add Row On Unmatched Condition


    Hi Brad,


    I couldnt actually see a way to speed up the code, I was just trying to simplify it and show that an easier way to delete or insert rows is to work backwards.
    Thanks for the speed check.

  • Re: Add Row On Unmatched Condition


    Quote from Reafidy

    Thanks for the speed check.


    No problems...I won't give you a ticket this time. : D Just make sure you're wearing your seat belt.


    LOL.


    Everyone's code looked good to me.

  • Re: Add Row On Unmatched Condition


    Quote from Reafidy

    Ha Ha, : D


    So you didnt notice the broken tail light and 2kg's of heroin in the back then? ;)


    So that's why my sniffer dog is off his face!?! : D

  • Re: Add Row On Unmatched Condition


    thanks guys for trying...if its the fastest its going to get then so be it. takes about a minute and a half on my computer to run because of the size of the list i guess. my computer is not exactly "slow" either...


    thanks again.

  • Re: Add Row On Unmatched Condition


    Quote from gmccreedy

    takes about a minute and a half on my computer to run because of the size of the list i guess. my computer is not exactly "slow" either...


    Sounds like a lot of data is being processed. There may quicker ways, but i'm not aware of any.


    You could possibly add a processing bar so the user at least knows something is going on. It might add a couple of seconds to your process, but sometimes I think it's worth it if it stops the potential idiot thinking something is wrong and they start screwing with your spreadsheet.

  • Re: Add Row On Unmatched Condition


    bradles,


    that is an excellent idea...and i was already pondering something like that. But actually, like i stated in the original post, this is only run once a month, so I just may suck it up and do it myself so no one can "f" it up. I may just do it with an On-Time method at night while no one is around and just leave my computer running, that way it can take as long as it wants...


    i was hoping to be able to allow the office manager to do this (who is not, shall we say, savvy). Looks like another "micro managing" situation...


    o well. If you guys think of ANYTHING though...please let me know!!


    Again, thanks for all your help...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!