Advanced Filter that Add Row Between Each Newly Encountered Criteria

[Mchlebo]

Very new. First Post.

I am building a tool for a manager to input employee schedules, see total hours and dollars in real time, and push out a csv file that will later be uploaded into our clock in system. So far, so good.

I have an advanced filter that pulls data for each employee based on what store they work at, sorted by department. The filter pulls data from an active directory to keep all employee data current (wage, store, dept, etc).

The team would prefer this advanced filter to also add a space between each department, so they can visually sort out a schedule for each.

Example of the current filter:
Dept
1
1
1
2
3
3
3
4

What they want to see:
Dept
1
1
1

2

3
3
3

4



My current VBA:

Sub AdvancedFilter()
'
' AdvancedFilter Macro
'

'
Range("A5").Select
Sheets("Data Source Extract").Range("Table5[#All]").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Sheets("Data Validation").Range("F1:J2"), _
CopyToRange:=Range("Table4[[Dept]:[Column10]]"), Unique:=False

End Sub

I hope this is enough info. Any advice would be much appreciated.

 

[Dryver14]

When are you wanting the spaces to be put in?

After you have copied the filtered range to Table 4?

 

[Mchlebo]

When are you wanting the spaces to be put in?

After you have copied the filtered range to Table 4?

That would probably work. Ideally it would be great for it to be built into the advanced filter process itself, so it knows to skip a row before adding the next department.

 

[Dryver14]

I may be wrong on this but I don't think inserting a row on a filtered column will necessarily place the row where it needs to be once unfiltered