On Apr 19, 5:18 pm, Jay wrote:
Yup. Filtering came to mind. Look into the SpecialCells method (with the
xlCellTypeVisible argument) to grab the visible cells after applying the
filter.
There are programmatic techniques to accumulate unique values , too. I
think they may be a bit faster than filtering, but I don't know if they'll be
substantially faster. Won't know until we try. Gotta go for now. Very
sorry for the delay.
---
Jay
" wrote:
On Apr 19, 4:36 pm, Jay wrote:
Hi Spy128Bit -
Thanks for the info. FYI: I'll be busy for the next 2-3 hours on some other
chores, but will take a look at your application then.
--
Jay
" wrote:
On Apr 19, 3:02 pm, Jay wrote:
Hi Spy128Bit -
Could you briefly describe your objective with this code? What do you want
it to do? Typcially, code speaks for itself, but your code does some things
that appear redundant (but may be perfectly appropriate). I just need a bit
more information before I can decide if I can provide any help. While you
and sali work on optimizing your existing code, maybe I could work on
alternatives.
One additional question to answer specifically: It appears that your code
will produce different values in column K for each instance of the same
person. In other words, if 'Joseph' appears multiple times throughout your
database, a different Z will be calculated each time because your code gets
the variables "Start" and "Finish" from the 'current record' and then
calculates Z relative to "Start" and "Finish". Is that correct ?
Jay
Jay,
The objective is to find the overlap in time for the same person.
Ex:
I worked 12:00 PM - 1:00PM on 4/1
I worked 12:30 PM - 1:00PM on 4/1
The value in "K" should would be 90 ( 60 for the time worked
12:00-1:00 and 30 for the overlap from the second line ).
The value in "K" on the second line would be 60 ( 30 for the time
worked 12:30-1:00PM and 30 for the overlap from the previous line ).
This is why the values in "K" are different for each line. What I
will be doing with the results is finding how productive someone is
based on their overlap.
If there's one thing I have seen on these boards it is alternative
solutions. Anything you can offer would be great. I'm glad to
receive the help I got so far and it's helped a lot. I hope that
helps explain the code a little better. If there's still questions on
something specific please let me know.
Thanks!- Hide quoted text -
- Show quoted text -
I'm actually looking at the option to do a unique value filter on
columb "B" and building an array from it. The array could then be
used as a filter for the processing piece of the visible rows only.
I'm hoping that should get it down to fairly quick in processing.- Hide quoted text -
- Show quoted text -
Ok, when the mind starts thinking I usually get myself in trouble in
the coding. How about a combination of
http://www.rondebruin.nl/copy5.htm#all
to either create all the sheets ( wow, fast ) or using it to somehow
pull a selection to another sheet, process it, then return the results
on a final sheet removing them off the data sheets as I go? Or stick
to what we got?