Please Help me with VBA on this large file

Siyanna

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
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
 
Last edited:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Try this You might need to substitute actual sheet names (in quotes) for the index numbers.

Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top