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:

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,434
Office Version
  1. 2013
Platform
  1. Windows
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:

Siyanna

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

Well-known Member
Joined
Feb 7, 2012
Messages
12,434
Office Version
  1. 2013
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,108,951
Messages
5,525,846
Members
409,667
Latest member
jwieting

This Week's Hot Topics

Top