Advance Filter using Macro or VBA

[Nikhil Patki]

On same sheet where we have original ( main ) data
i'm trying to extract specific data based on criteria using advance filter feature on same sheet as original data exists
but on a different location from main data.
when i try to refresh it changing the criteria, macro only refresh the one column that is first.

 

[Aniruddah Alam]

On same sheet where we have original ( main ) data
i'm trying to extract specific data based on criteria using advance filter feature on same sheet as original data exists
but on a different location from main data.
when i try to refresh it changing the criteria, macro only refresh the one column that is first.

Hello Nikhil,

Thank you for posting your query on Exceldemy Forum. However, without examining how you have organized your data and utilized the advanced filter feature in conjunction with a VBA macro, it's not possible for us to offer a workable solution. Would you be able to provide a sample file for me to review so that I can better understand the issue and suggest a viable solution?

Regards
Aniruddah
Team Exceldemy

 

[Nikhil Patki]

Dear Admin ! thank you for your reply.
kindly find attached file in which i'm trying to refresh advance filter
using macro recorder

 

[Lutfor Rahman Shimanto]

Dear Admin ! thank you for your reply.
kindly find attached file in which i'm trying to refresh advance filter
using macro recorder

Hello Nikhil Patki

Welcome to ExcelDemy Form. Thanks for reaching out and sharing your problem. You said you have a Macro that applies an advanced filter, but after changing the criteria and refreshing, the macro only refreshes the first column. You have to clear the contents of the previous destination range in the sheet named Data for your existing recorded macro to work properly.

I have developed another sub-procedure, ApplyAdvancedFilter, which will fulfil your goal of applying an advanced filter. Besides, I am presenting an Event procedure called the ApplyAdvancedFilter when we have changed criteria (When changing the criteria, we must change the criteria heading first.)

Step 1: Right-click on the sheet name tab => Click on View Code => Paste the following code in the sheet module => Save.

Private Sub Worksheet_Change(ByVal Target As Range)
  
    Dim ws As Worksheet
    Dim changedCell As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")
  
    Set changedCell = ws.Range("H2")

    If Not Intersect(Target, changedCell) Is Nothing Then
        Call ApplyAdvancedFilter
    End If

End Sub

Sub ApplyAdvancedFilter()

    Dim ws As Worksheet
    Dim dataRange As Range
    Dim criteriaRange As Range
    Dim filteredRange As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
  
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
  
    Set dataRange = ws.Range("A1:F" & lastRow)

    Set criteriaRange = ws.Range("H1:H2")
  
    ws.Range("H5:M" & ws.Cells(ws.Rows.Count, "H").End(xlUp).Row).Borders.LineStyle = xlNone
    ws.Range("H5:M" & ws.Cells(ws.Rows.Count, "H").End(xlUp).Row).ClearContents

    Set filteredRange = ws.Range("H5")

    dataRange.AdvancedFilter Action:=xlFilterCopy, criteriaRange:=criteriaRange, CopyToRange:=filteredRange, Unique:=False

End Sub

​

Step 2: Return to Sheet1 and make desired changes to see the output like the following GIF.

​

Things to remember:
When using a date as a criterion, ensure both source and criteria in the date format are the same.

I am also attaching the solution workbook for better understanding. Good luck.

Regards
Lutfor Rahman Shimanto

 

[Lutfor Rahman Shimanto]

Dear Admin ! thank you for your reply.
kindly find attached file in which i'm trying to refresh advance filter
using macro recorder

Dear Nikhil Patki

Thanks for sharing your dataset. I have modified my previous code in such a way that you can use it in your workbook.

Likewise, paste the following code into the sheet (Data) module and save the workbook.

Sub-procedure & Event Procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim ws As Worksheet
    Dim changedCell As Range

    Set ws = ThisWorkbook.Sheets("Data")
    
    Set changedCell = ws.Range("J2")

    If Not Intersect(Target, changedCell) Is Nothing Then
        Call ApplyAdvancedFilter
    End If

End Sub

Sub ApplyAdvancedFilter()

    Dim ws As Worksheet
    Dim dataRange As Range
    Dim criteriaRange As Range
    Dim filteredRange As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Sheets("Data")
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Set dataRange = ws.Range("A1:F" & lastRow)

    Set criteriaRange = ws.Range("J1:J2")
    
    If ws.Cells(ws.Rows.Count, "J").End(xlUp).Row > 2 Then
        ws.Range("J4:O" & ws.Cells(ws.Rows.Count, "J").End(xlUp).Row).Borders.LineStyle = xlNone
        ws.Range("J4:O" & ws.Cells(ws.Rows.Count, "J").End(xlUp).Row).ClearContents
    End If

    Set filteredRange = ws.Range("J4")

    dataRange.AdvancedFilter Action:=xlFilterCopy, criteriaRange:=criteriaRange, CopyToRange:=filteredRange, Unique:=False

End Sub

OUTPUT:

​

Hopefully, the idea will help you. Good luck.

Regards
Lutfor Rahman Shimanto

 

[Nikhil Patki]

Really Thank you Admin.
I've copied your code in my sheet and modified the range as actual.
It's working.

But still there is one problem.
as when i clear all filtered data manually
and press the macro assigned button / shape
initially it clears the criteria and brings all data.
secondly after data + criteria is cleared
and I use macro assigned button, it clear evens criteria heading
later when i input values in the cell it provides the filtered data without hassle.
everything is working fine. Only i'm unable to understand why after filtered data range is cleared
1. why it clears the criteria ( cell H2 ) on using macro assigned button
2. later again after clearing data, why it clears even header also of criteria ( cell H1 )

 

[Lutfor Rahman Shimanto]

Really Thank you Admin.
I've copied your code in my sheet and modified the range as actual.
It's working.

Hello Nikhil Patki

You are welcome. Your appreciation means a lot to us. We are always here for your Excel & VBA-related queries and many more.

According to your dataset, I have developed an Event Procedure and a Sub-procedure. I am glad to hear that it is working fine for your dataset.

Regards
Lutfor Rahman Shimanto

 

[Lutfor Rahman Shimanto]

Really Thank you Admin.
I've copied your code in my sheet and modified the range as actual.
It's working.

But still there is one problem.
as when i clear all filtered data manually
and press the macro assigned button / shape
initially it clears the criteria and brings all data.
secondly after data + criteria is cleared
and I use macro assigned button, it clear evens criteria heading
later when i input values in the cell it provides the filtered data without hassle.
everything is working fine. Only i'm unable to understand why after filtered data range is cleared
1. why it clears the criteria ( cell H2 ) on using macro assigned button
2. later again after clearing data, why it clears even header also of criteria ( cell H1 )

Dear Nikhil Patki

You probably worked on modifying the first sub-procedure I developed earlier (before sharing your dataset) based on a dataset I created to demonstrate your problem. After sharing your dataset, I have modified the sub-procedure that is currently working for your dataset.

Several reasons exist for my previous sub-procedure malfunction when working with your shared dataset. In my demonstrated dataset, a cell displays "Result," and filter data are displayed after that row. In my previous sub-procedure, when clearing previously filtered data, it calculates the last row of column H. If you manually clear the previously filtered values, you do not have another non-empty cell like my demonstrated dataset, and the sub-procedure will clear the heading.

So, please use the sub-procedure which I developed for your dataset. You can also assign it in a button or any shape. Good luck!

Regards
Lutfor Rahman Shimanto

 

[Nikhil Patki]

Dear Nikhil Patki

Thanks for sharing your dataset. I have modified my previous code in such a way that you can use it in your workbook.

Likewise, paste the following code into the sheet (Data) module and save the workbook.

Sub-procedure & Event Procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim ws As Worksheet
    Dim changedCell As Range

    Set ws = ThisWorkbook.Sheets("Data")
   
    Set changedCell = ws.Range("J2")

    If Not Intersect(Target, changedCell) Is Nothing Then
        Call ApplyAdvancedFilter
    End If

End Sub

Sub ApplyAdvancedFilter()

    Dim ws As Worksheet
    Dim dataRange As Range
    Dim criteriaRange As Range
    Dim filteredRange As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Sheets("Data")
   
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   
    Set dataRange = ws.Range("A1:F" & lastRow)

    Set criteriaRange = ws.Range("J1:J2")
   
    If ws.Cells(ws.Rows.Count, "J").End(xlUp).Row > 2 Then
        ws.Range("J4:O" & ws.Cells(ws.Rows.Count, "J").End(xlUp).Row).Borders.LineStyle = xlNone
        ws.Range("J4:O" & ws.Cells(ws.Rows.Count, "J").End(xlUp).Row).ClearContents
    End If

    Set filteredRange = ws.Range("J4")

    dataRange.AdvancedFilter Action:=xlFilterCopy, criteriaRange:=criteriaRange, CopyToRange:=filteredRange, Unique:=False

End Sub

OUTPUT:

View attachment 1069
​

Hopefully, the idea will help you. Good luck.

Regards
Lutfor Rahman Shimanto

It was really grateful of you. Now it's working totally fine as expected.

 

[Nikhil Patki]

I have one more query.
I'm attempting to build a attendance muster
This includes multiple sheets -
1. Employee Record which will have all employee list even if someone has left and few are newly added
2. Active Employee which contains only active employees and not have employees who have left
3. Muster which contain their monthly attendance
4. Salary record which contains only employee basic details and their salary details for that month
5. Salary Slip. this sheet has been modified such as only employee code can be selected and rest data fields are set using vlookup and direct link
in such a way that when employee code is changed it automatically updates all fields showing selected employees salary details in the set format

Now I've only two issues
1. to get data filtered in sheet 2 as per the criteria " active " from sheet 1
but i want only selective headers to arrive and not all headings
as sheet 4 data is linked from sheet 2

2. in sheet 5 i want Two buttons activated having macro
One for Print the salary slip which is manageable with my knowledge - one at a time
but Second button to generate pdf using employee name.

as employee data will be changing multiples time in year
and my knowledge being of basic level
i can't have all employee's salary slip generated from salary sheet, as well as their slips pdf generated or printed in one click