refine advanced filter

R

raccoon588

Board Regular
Joined
Aug 5, 2016
Messages
118
Is there a better, more efficient way to complete all these advanced filters?


Code: 
Sub Sort()


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!l4:M5"), CopyToRange:=Range("Calendar!l8:m15"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!n4:o5"), CopyToRange:=Range("Calendar!n8:o15"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!p4:q5"), CopyToRange:=Range("Calendar!p8:q15"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!r4:s5"), CopyToRange:=Range("Calendar!r8:s15"), Unique:=False


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!t4:u5"), CopyToRange:=Range("Calendar!t8:u15"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!v4:w5"), CopyToRange:=Range("Calendar!v8:w15"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!x4:y5"), CopyToRange:=Range("Calendar!x8:y15"), Unique:=False
        
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!l16:M17"), CopyToRange:=Range("Calendar!l20:m27"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!n16:o17"), CopyToRange:=Range("Calendar!n20:o27"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!p16:q17"), CopyToRange:=Range("Calendar!p20:q27"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!r16:s17"), CopyToRange:=Range("Calendar!r20:s27"), Unique:=False


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!t16:u17"), CopyToRange:=Range("Calendar!t20:u27"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!v16:w17"), CopyToRange:=Range("Calendar!v20:w27"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!x16:y17"), CopyToRange:=Range("Calendar!x20:y27"), Unique:=False
        


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!l28:M29"), CopyToRange:=Range("Calendar!l32:m39"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!n28:o29"), CopyToRange:=Range("Calendar!n32:o39"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!p28:q29"), CopyToRange:=Range("Calendar!p32:q39"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!r28:s29"), CopyToRange:=Range("Calendar!r32:s39"), Unique:=False


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!t28:u29"), CopyToRange:=Range("Calendar!t32:u39"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!v28:w29"), CopyToRange:=Range("Calendar!v32:w39"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!x28:y29"), CopyToRange:=Range("Calendar!x32:y39"), Unique:=False
        
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!l40:M41"), CopyToRange:=Range("Calendar!l44:m51"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!n40:o41"), CopyToRange:=Range("Calendar!n44:o51"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!p40:q41"), CopyToRange:=Range("Calendar!p44:q51"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!r40:s41"), CopyToRange:=Range("Calendar!r44:s51"), Unique:=False


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!t40:u41"), CopyToRange:=Range("Calendar!t44:u51"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!v40:w41"), CopyToRange:=Range("Calendar!v44:w51"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!x40:y41"), CopyToRange:=Range("Calendar!x44:y51"), Unique:=False
        
        
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!l52:M53"), CopyToRange:=Range("Calendar!l56:m63"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!n52:o53"), CopyToRange:=Range("Calendar!n56:o63"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!p52:q53"), CopyToRange:=Range("Calendar!p56:q63"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!r52:s53"), CopyToRange:=Range("Calendar!r56:s63"), Unique:=False


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!t52:u53"), CopyToRange:=Range("Calendar!t56:u63"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!v52:w53"), CopyToRange:=Range("Calendar!v56:w63"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!x52:y53"), CopyToRange:=Range("Calendar!x56:y63"), Unique:=False
        
        
        
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!l64:M65"), CopyToRange:=Range("Calendar!l68:m75"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!n64:o65"), CopyToRange:=Range("Calendar!n68:o75"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!p64:q65"), CopyToRange:=Range("Calendar!p68:q75"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!r64:s65"), CopyToRange:=Range("Calendar!r68:s75"), Unique:=False


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!t64:u65"), CopyToRange:=Range("Calendar!t68:u75"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!v64:w65"), CopyToRange:=Range("Calendar!v68:w75"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!x64:y65"), CopyToRange:=Range("Calendar!x68:y75"), Unique:=False
End Sub
 
Last edited:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Hello,

You could test the following :

Code: 
Sub RefineAdvFilter()
Dim i As Long
Dim j As Long
  For i = 4 To 64 Step 12
    For j = 12 To 24
      With Sheets("RequestLog").Columns("A:G")
        .AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!" & Nb2Let(j) & i & ":" & Nb2Let(j + 1) & i + 1), CopyToRange:=Range("Calendar!" & Nb2Let(j) & i + 4 & ":" & Nb2Let(j + 1) & i + 11), Unique:=False
      End With
    Next j
  Next i
End Sub


Private Function Nb2Let(lng As Long) As String
'Convert To Column Letter
  Nb2Let = Split(Cells(1, lng).Address, "$")(1)
End Function

Hope this will help
 
Upvote 0
Its still very slow. Maybe explaining my end goal would help with a better solution. I have a calendar, each advanced filter in my above code sorted out a master list based on the date in the cell. Once everything is sorted they show up in list boxes that are located on each day. the day changes in the calendar depending on what month they click on so cell B4 may be August1 but if they advance the calendar to the next month cell B4 may now represent September5. Right now, the code i have above runs every time someone changes the month so the proper data shows in the list box under the corresponding date. Maybe there is a better and different way entirety to go about it?


could INDIRECT help?
 
Last edited:
Upvote 0
Funny you did not mention that the problem was the macro is very slow ... in your very first message ... !!!

Could you at least confirm the proposed macro does function as expected ...
 
Upvote 0
raccoon588 said:
It puts some of the data in the wrong columns but seems to headed in the direction i am after.
Click to expand...

Your comment is not very precise ...

By the way ... which macro is very slow ...?

Are both macros equally slow ...?
 
Upvote 0
Hello again,

Is there an improvement with this version :

Code: 
Sub RefineAdvFilter()
Dim i As Long
Dim j As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
  For i = 4 To 64 Step 12
    For j = 12 To 24
      With Sheets("RequestLog").Columns("A:G")
        .AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!" & Nb2Let(j) & i & ":" & Nb2Let(j + 1) & i + 1), CopyToRange:=Range("Calendar!" & Nb2Let(j) & i + 4 & ":" & Nb2Let(j + 1) & i + 11), Unique:=False
      End With
    Next j
  Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

HTH
 
Upvote 0
It functions so much faster but some of the wrong info is pulled in, its like the columns are off for where it is placing the data, how do i shift that?
 
Last edited:
Upvote 0
Hopefully the following helps:
here are the columns the advanced filter populates and how they are populating.

[TABLE="width: 1400"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]Approved Time Off this Month[/TD]
[TD="colspan: 2"]Approved Time Off this Month[/TD]
[TD="colspan: 2"]Approved Time Off this Month[/TD]
[TD="colspan: 2"]Approved Time Off this Month[/TD]
[TD="colspan: 2"]Approved Time Off this Month[/TD]
[TD="colspan: 2"]Approved Time Off this Month[/TD]
[TD="colspan: 2"]Approved Time Off this Month[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Full Day[/TD]
[TD]Furlong, Ross[/TD]
[TD]Full Day[/TD]
[TD]Furlong, Ross[/TD]
[TD]Second Half[/TD]
[TD]Kelley, Lia[/TD]
[TD][/TD]
[TD][/TD]
[TD]Full Day[/TD]
[TD]Bezio, Wanda[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


here are the columns the advanced filter populates and how they should populate. You'll see that the names are being put into the right column but the types of day need to be moved over to the next column from where they're currently being placed.

[TABLE="width: 1400"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]Approved Time Off this Month[/TD]
[TD="colspan: 2"]Approved Time Off this Month[/TD]
[TD="colspan: 2"]Approved Time Off this Month[/TD]
[TD="colspan: 2"]Approved Time Off this Month[/TD]
[TD="colspan: 2"]Approved Time Off this Month[/TD]
[TD="colspan: 2"]Approved Time Off this Month[/TD]
[TD="colspan: 2"]Approved Time Off this Month[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Furlong, Ross[/TD]
[TD]Full Day[/TD]
[TD]Furlong, Ross[/TD]
[TD]Full Day[/TD]
[TD]Kelley, Lia[/TD]
[TD]Second Half[/TD]
[TD][/TD]
[TD][/TD]
[TD]Bezio, Wanda[/TD]
[TD]Full Day[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Advanced Filter Macro works until I run a different Advanced Filter Macro in same workbook
Replies
1
Views
413
rdarrylhudson
R
A
  • Question Question
Advanced Filter Macro with subtotal?
Replies
0
Views
295
Add564656
A
S
  • Solved
add a message box if a match using a userform list box doesn't match
Replies
7
Views
611
still learning
S
J
  • Question Question
copy, paste special, resize & offset
Replies
0
Views
353
jdcar
J
Z
  • Solved
VBA: Advanced Filter Limitations
Replies
3
Views
619
zero269
Z

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

Share this page

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