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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.

Approved Time Off this MonthApproved Time Off this MonthApproved Time Off this MonthApproved Time Off this MonthApproved Time Off this MonthApproved Time Off this MonthApproved Time Off this Month
NameTypeNameTypeNameTypeNameTypeNameTypeNameTypeNameType
Full DayFurlong, RossFull DayFurlong, RossSecond HalfKelley, LiaFull DayBezio, Wanda

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>


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.

Approved Time Off this MonthApproved Time Off this MonthApproved Time Off this MonthApproved Time Off this MonthApproved Time Off this MonthApproved Time Off this MonthApproved Time Off this Month
NameTypeNameTypeNameTypeNameTypeNameTypeNameTypeNameType
Furlong, RossFull DayFurlong, RossFull DayKelley, LiaSecond HalfBezio, WandaFull Day

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Advanced Filter Macro works until I run a different Advanced Filter Macro in same workbook
Replies
1
Views
99
rdarrylhudson
R
A
  • Question
Advanced Filter Macro with subtotal?
Replies
0
Views
113
Add564656
A
S
  • Solved
add a message box if a match using a userform list box doesn't match
Replies
7
Views
125
still learning
S
J
  • Question
copy, paste special, resize & offset
Replies
0
Views
174
jdcar
J
T
  • Question
Multiple filters on filtered list
Replies
1
Views
212
Cubist
Cubist

Forum statistics

Threads
1,214,875
Messages
6,122,044
Members
449,063
Latest member
ak94

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