No results (or only 1 results) shown with Advanced Filter

S

stanco

New Member
Joined
Mar 16, 2019
Messages
48
Hi and happy new year to you!


I have a table which consist of data from Column A to AM. In the table, there are some blanks as it is still pending follow up from users.

I am trying to do an advanced filter on another sheet based on the approval date (Column AH) so that we can filter out the entries that were approved on certain date while the rest of my colleagues work on the main table. i have done a macro to do that but it seems like it is not working.

Would anyone be able to assist please?

Thank you.

Sample File
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Hi Stanco,

Below is the updated macro.

Please check if it fulfils the requirement. In filter criteria, mention only date (see image). The result will be equals to or greater than specified date.

VBA Code: 
Sub DisplayResults()

    Dim lastRow As Integer, counter As Integer, start As Integer, alreadyExistRow As Integer
    
    start = 7
    
    lastRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    alreadyExistRow = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
    
    'to clear already existing data in sheet3
    If alreadyExistRow > 6 Then Sheets("Sheet3").Range("A7:L" & alreadyExistRow).ClearContents

    Application.ScreenUpdating = False
    With Sheets("Sheet2")
        For counter = 2 To lastRow
            If .Range("AH" & counter) >= Sheets("Sheet3").Range("A2") Then
                Sheets("Sheet3").Cells(start, 1) = .Range("A" & counter)
                Sheets("Sheet3").Cells(start, 2) = .Range("C" & counter)
                Sheets("Sheet3").Cells(start, 3) = .Range("D" & counter)
                Sheets("Sheet3").Cells(start, 4) = .Range("H" & counter)
                Sheets("Sheet3").Cells(start, 5) = .Range("AB" & counter)
                Sheets("Sheet3").Cells(start, 6) = .Range("AF" & counter)
                Sheets("Sheet3").Cells(start, 7) = .Range("AG" & counter)
                Sheets("Sheet3").Cells(start, 8) = .Range("AH" & counter)
                Sheets("Sheet3").Cells(start, 9) = .Range("AI" & counter)
                Sheets("Sheet3").Cells(start, 10) = .Range("AJ" & counter)
                Sheets("Sheet3").Cells(start, 11) = .Range("AK" & counter)
                Sheets("Sheet3").Cells(start, 12) = .Range("AL" & counter)
                start = start + 1
            End If
        Next
    End With
    
 
    Sheets("Sheet3").Range("G:J").NumberFormat = "DD-MMM-YY"

End Sub

Thanks,
Saurabh
 

Attachments

  • datefilter.PNG
    datefilter.PNG
    87.5 KB · Views: 7
Upvote 0
Cell A1 needs to contain the exact text of the header of the column you want to filter on. If you copy and paste the 'Approval Date' text (including the line feed character) from the table header and paste it to A1, your filter should work.
 
Upvote 0
Saurabhj said:
Hi Stanco,

Below is the updated macro.

Please check if it fulfils the requirement. In filter criteria, mention only date (see image). The result will be equals to or greater than specified date.

VBA Code: 
Sub DisplayResults()

    Dim lastRow As Integer, counter As Integer, start As Integer, alreadyExistRow As Integer
   
    start = 7
   
    lastRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    alreadyExistRow = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
   
    'to clear already existing data in sheet3
    If alreadyExistRow > 6 Then Sheets("Sheet3").Range("A7:L" & alreadyExistRow).ClearContents

    Application.ScreenUpdating = False
    With Sheets("Sheet2")
        For counter = 2 To lastRow
            If .Range("AH" & counter) >= Sheets("Sheet3").Range("A2") Then
                Sheets("Sheet3").Cells(start, 1) = .Range("A" & counter)
                Sheets("Sheet3").Cells(start, 2) = .Range("C" & counter)
                Sheets("Sheet3").Cells(start, 3) = .Range("D" & counter)
                Sheets("Sheet3").Cells(start, 4) = .Range("H" & counter)
                Sheets("Sheet3").Cells(start, 5) = .Range("AB" & counter)
                Sheets("Sheet3").Cells(start, 6) = .Range("AF" & counter)
                Sheets("Sheet3").Cells(start, 7) = .Range("AG" & counter)
                Sheets("Sheet3").Cells(start, 8) = .Range("AH" & counter)
                Sheets("Sheet3").Cells(start, 9) = .Range("AI" & counter)
                Sheets("Sheet3").Cells(start, 10) = .Range("AJ" & counter)
                Sheets("Sheet3").Cells(start, 11) = .Range("AK" & counter)
                Sheets("Sheet3").Cells(start, 12) = .Range("AL" & counter)
                start = start + 1
            End If
        Next
    End With
   

    Sheets("Sheet3").Range("G:J").NumberFormat = "DD-MMM-YY"

End Sub

Thanks,
Saurabh
Click to expand...

Hi Saurabh, happy new year to you.

This works great, but how should i amend the code if I want to have a "from date" to "till date" in A1:B2?
 
Upvote 0
RoryA said:
Cell A1 needs to contain the exact text of the header of the column you want to filter on. If you copy and paste the 'Approval Date' text (including the line feed character) from the table header and paste it to A1, your filter should work.
Click to expand...

Hi RoryA, thanks and happy new year to you. I think Saurabh VBA code works better than my advanced filter. I just need him (or anyone else) to help me improve on it a little.
 
Upvote 0
Hi Stanco,

Please make below change in your code (date comparison):
Also check image to change the Sheet 3 for from and To date. Cell A2 is for From Date and Cell B2 is for To Date.

VBA Code: 
Sub DisplayResults()

    Dim lastRow As Integer, counter As Integer, start As Integer, alreadyExistRow As Integer

    start = 7
    
    lastRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    alreadyExistRow = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
    
    'to clear already existing data in sheet3
    If alreadyExistRow > 6 Then Sheets("Sheet3").Range("A7:L" & alreadyExistRow).ClearContents

    Application.ScreenUpdating = False
    With Sheets("Sheet2")
        For counter = 2 To lastRow
            If .Range("AH" & counter) >= Sheets("Sheet3").Range("A2") And _
            .Range("AH" & counter) <= Sheets("Sheet3").Range("B2") Then
                Sheets("Sheet3").Cells(start, 1) = .Range("A" & counter)
                Sheets("Sheet3").Cells(start, 2) = .Range("C" & counter)
                Sheets("Sheet3").Cells(start, 3) = .Range("D" & counter)
                Sheets("Sheet3").Cells(start, 4) = .Range("H" & counter)
                Sheets("Sheet3").Cells(start, 5) = .Range("AB" & counter)
                Sheets("Sheet3").Cells(start, 6) = .Range("AF" & counter)
                Sheets("Sheet3").Cells(start, 7) = .Range("AG" & counter)
                Sheets("Sheet3").Cells(start, 8) = .Range("AH" & counter)
                Sheets("Sheet3").Cells(start, 9) = .Range("AI" & counter)
                Sheets("Sheet3").Cells(start, 10) = .Range("AJ" & counter)
                Sheets("Sheet3").Cells(start, 11) = .Range("AK" & counter)
                Sheets("Sheet3").Cells(start, 12) = .Range("AL" & counter)
                start = start + 1
            End If
        Next
    End With
    
 
    Sheets("Sheet3").Range("G:J").NumberFormat = "DD-MMM-YY"
   
    Application.ScreenUpdating = True
End Sub

Thanks,
Saurabh
 

Attachments

  • DateFromTo.PNG
    DateFromTo.PNG
    66.9 KB · Views: 10
Upvote 0
Solution
Saurabhj said:
Hi Stanco,

Please make below change in your code (date comparison):
Also check image to change the Sheet 3 for from and To date. Cell A2 is for From Date and Cell B2 is for To Date.

VBA Code: 
Sub DisplayResults()

    Dim lastRow As Integer, counter As Integer, start As Integer, alreadyExistRow As Integer

    start = 7
   
    lastRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    alreadyExistRow = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
   
    'to clear already existing data in sheet3
    If alreadyExistRow > 6 Then Sheets("Sheet3").Range("A7:L" & alreadyExistRow).ClearContents

    Application.ScreenUpdating = False
    With Sheets("Sheet2")
        For counter = 2 To lastRow
            If .Range("AH" & counter) >= Sheets("Sheet3").Range("A2") And _
            .Range("AH" & counter) <= Sheets("Sheet3").Range("B2") Then
                Sheets("Sheet3").Cells(start, 1) = .Range("A" & counter)
                Sheets("Sheet3").Cells(start, 2) = .Range("C" & counter)
                Sheets("Sheet3").Cells(start, 3) = .Range("D" & counter)
                Sheets("Sheet3").Cells(start, 4) = .Range("H" & counter)
                Sheets("Sheet3").Cells(start, 5) = .Range("AB" & counter)
                Sheets("Sheet3").Cells(start, 6) = .Range("AF" & counter)
                Sheets("Sheet3").Cells(start, 7) = .Range("AG" & counter)
                Sheets("Sheet3").Cells(start, 8) = .Range("AH" & counter)
                Sheets("Sheet3").Cells(start, 9) = .Range("AI" & counter)
                Sheets("Sheet3").Cells(start, 10) = .Range("AJ" & counter)
                Sheets("Sheet3").Cells(start, 11) = .Range("AK" & counter)
                Sheets("Sheet3").Cells(start, 12) = .Range("AL" & counter)
                start = start + 1
            End If
        Next
    End With
   

    Sheets("Sheet3").Range("G:J").NumberFormat = "DD-MMM-YY"
  
    Application.ScreenUpdating = True
End Sub

Thanks,
Saurabh
Click to expand...

Thank you so much Saurabh! :D
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Filter date time data so I only get one data point per hour
2 3
Replies
24
Views
505
Peter_SSs
Peter_SSs
J
  • Question
Filter Function with OR being similar
Replies
3
Views
166
kevin9999
K
J
  • Solved
Advanced Filter using a date as the criteria
Replies
5
Views
338
Alex Blakenburg
A
N
  • Question
Locking a row once a value is entered into a cell
Replies
9
Views
718
arturojab
A
R
  • Question
Advanced Filter Macro works until I run a different Advanced Filter Macro in same workbook
Replies
1
Views
108
rdarrylhudson
R

Forum statistics

Threads
1,215,059
Messages
6,122,917
Members
449,093
Latest member
dbomb1414

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