Excel VBA Autofilter date between two cells in each rows

Can someone help me to fix this?

Currently, I am using the below code and it is working fine. I have dates in Columns "G" and "J". Now I need to add one more filter on the column "G" (Field:=7). If the date in column "G" is greater than the date in Column "J" (or) the column "J" is Blank cell, Then it needs to be filtered. It would be greatly appreciated. Thank you.

Also, I have attached the Excel file. In case you want to check this. Thank you.

Book1.xlsm

Sub TEST()

Dim outstandingmarkupsdwgrng As Range
Set outstandingmarkupsdwgrng = Nothing

Application.ScreenUpdating = False

With Worksheets("Drawing log")
    .AutoFilterMode = False
    .Range("A8").AutoFilter Field:=4, Operator:=xlFilterValues, Criteria1:=Array("Dwg Status", "APP", "R&R")
    .Range("A8").AutoFilter Field:=6, Operator:=xlFilterValues, Criteria1:="<>Completely released"
    .Range("A8").AutoFilter Field:=9, Operator:=xlFilterValues, Criteria1:="<>AE markups in hand"
            With .AutoFilter.Range
                    On Error Resume Next
                    Set outstandingmarkupsdwgrng = .Resize(.Rows.Count - 1, 5).Offset(1, 2).SpecialCells(xlCellTypeVisible)
                    
                    On Error GoTo 0
                    If Not outstandingmarkupsdwgrng Is Nothing Then
                        outstandingmarkupsdwgrng.Copy Worksheets("Sheet1").Range("A1")
                    End If
                    
                End With
    .AutoFilterMode = False
End With

Application.ScreenUpdating = True

End Sub

Hi Bernie Deitrick,

Understood. I can do that if it is a new file. But I have around 500 existing files that I can't add a column in it. it is very difficult for now.

See if this works as you explaind.

Sub test()
    Dim r As Range
    Sheets("sheet1").[a1].CurrentRegion.Clear
    With Sheets("drawing log")
        Set r = .[q1:q2]
        r(2).Formula = "=and(isnumber(match(d9,{""Dwg Status"",""APP"",""R&R""},0))," & _
        "f9<>""Completely released"",i9<>""Ae markup in hand"",or(g9>j9,j9=""""))"
        With .Range("b8", .Range("b" & .Rows.Count).End(xlUp)).Resize(, 9)
            .Offset(, 1).Rows(1).Resize(, 5).Copy Sheets("sheet1").[a1]
            .AdvancedFilter 2, r, Sheets("sheet1").[a1].CurrentRegion
        End With
        r = ""
    End With
End Sub

Hi Jindon, Yes, It is working very fine. Thank you so much for your help

I have one more question, In the output data, I am getting the following text "01/00/00" in column "D" (refer to below image). Is it possible to replace/remove this text?

Attachment 846266

According to the attachment an Excel basics VBA demonstration for starters :

PHP Code: 

Sub Demo1()
        Sheet1.UsedRange.Offset(1).Clear
   With Sheet3
       .[M9].Formula = "=((D9=""APP"")+(D9=""R&R""))*(F9<>""Completely released"")*(I9<>""AE markups in hand"")*(G9>J9)"
       .[B8:J201].AdvancedFilter 2, .[M8:M9], Sheet1.[A1:E1]
   End With
        Sheet1.UsedRange.Borders.Weight = 2
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Hi Marc L, Thanks for your response. But I am getting a runtime error 1004 (Attachment 846267). However the code given by Jindon is working very fine. Thank you.

Originally Posted by rajeshmuthu

Hi Marc L, Thanks for your response. But I am getting a runtime error 1004

No such issue with your initial post attachment …

Attachment 846266 in post #7 is invalid.

try this

Sub test()
    Dim r As Range, s As String
    Sheets("sheet1").[a1].CurrentRegion.Clear
    With Sheets("drawing log")
        Set r = .[q1:q2]
        r(2).Formula = "=and(isnumber(match(d9,{""Dwg Status"",""APP"",""R&R""},0))," & _
        "f9<>""Completely released"",i9<>""Ae markup in hand"",or(g9>j9,j9=""""))"
        With .Range("b8", .Range("b" & .Rows.Count).End(xlUp)).Resize(, 9)
            .Offset(, 1).Rows(1).Resize(, 5).Copy Sheets("sheet1").[a1]
            .AdvancedFilter 2, r, Sheets("sheet1").[a1].CurrentRegion
        End With
        r = ""
    End With
    With Sheets("sheet1").[a1].CurrentRegion
        s = .Range("d2").NumberFormatLocal
        With .Columns("d").Offset(1)
            .NumberFormat = ""
            .Replace 0, "", 1
            .NumberFormatLocal = s
        End With
    End With
End Sub

Hi Jindon, It is working very fine. Thank you so much for your help.

Hi Jindan, I have one more issue when I tried to use this code in all other Excel files, I got the following error "Run-time error '1004' The extract range has missing or invalid field name" in the below location. and the title of the columns has come as "#REF!", Because I had provided the reference formula text in the header text from another worksheet (like ='Weight log'!C7, ='Weight log'!BR7, ='Weight log'!E7) in the excel file. Is it possible to copy the header text (instead of copying formula) into sheet1?

.AdvancedFilter 2, r, Sheets("sheet1").[a1].CurrentRegion

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

try change

        With .Range("b8", .Range("b" & .Rows.Count).End(xlUp)).Resize(, 9)
            .Offset(, 1).Rows(1).Resize(, 5).Copy Sheets("sheet1").[a1]
            .AdvancedFilter 2, r, Sheets("sheet1").[a1].CurrentRegion
        End With

to

        With .Range("b8", .Range("b" & .Rows.Count).End(xlUp)).Resize(, 9)
            Sheets("sheet1").Range("a1").Resize(, 5).Value = .Offset(, 1).Rows(1).Resize(, 5).Value
            .AdvancedFilter 2, r, Sheets("sheet1").[a1].CurrentRegion
        End With

Yes, It is working very fine. Thank you Jindon.

Hi Jindon, I need a help. Is it possible to get the output of this range of values in a separate variable (instead of copying it into worksheet "Sheet1")? So that I can create a new email and add these output range values to the body of the email. Thank you.

 With .Range("b8", .Range("b" & .Rows.Count).End(xlUp)).Resize(, 9)
            Sheets("sheet1").Range("a1").Resize(, 5).Value = .Offset(, 1).Rows(1).Resize(, 5).Value
            .AdvancedFilter 2, r, Sheets("sheet1").[a1].CurrentRegion
        End With

I just don't understand what you are asking.

Hi Jindon, Sorry I wasn't able to explain clearly. I would like to copy this filtered range table (output data) and paste it into the email body. So that I can send those details (through email) to our manager. Is it possible? Please advise...

Possible, but you need to know the basics of HTML with HTMLBody property.

So better open a new thread for this.

ok Jindon. I will open a new thread. Thank you.