Please Help me with VBA on this large file

[Siyanna]

Hi All

I have 2 sheets with almost 300k rows of data..

The columns are from A to Q
the dates, agent name and start times and times are sorted to sort each agent schedule that day

I need to have a macro that loops through every day and checks to see if an agent had any activity that say Lunch, if it does i need to cut that whole shift (that days shift and paste it into the sheet called - (Not Needed)

Date in Col A
Agent Name in F
Agent Activity in Col G
Start time of activity in H
End time of activity in I

I cant process it in the sheet straight away it takes forever to calculate hence why an array approach should be better and the paste the values in at the end but im struggling to do that

Please can someone help me

 

[JLGWhiz]

Try this You might need to substitute actual sheet names (in quotes) for the index numbers.

Sub t()
With Sheets(1).UsedRange
    .AutoFilter 7, "Lunch"
    .Offset(1).SpecialCells(xlCellTypeVisible).Copy Sheets(2).Cells(Rows.Count, 1).End(xlUp)(2)
    .AutoFilterMode = False
End With
End Sub

 

[Siyanna]

Hi

Thank you - this won’t work for my example

There are many activities on that day that are not lunch - I just need to see if that person had lunch that day and if they did then all that shift that day including all the other activities need to be cut and pasted to the not needed sheet. This needs to be repeated for each agent on for everyday

Hope that makes sense

 

[JLGWhiz]

if that person had lunch that day and if they did then all that shift that day including all the other activities need to be cut and pasted

I would need to see the worksheet to be able to code this type criteria. I cannot get a good concept of the data layout and how to determine what applies to a shift from the info preiviously provided.