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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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 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

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.

Attachment 846266 in post #7 is invalid.

try this

Please Login or Register  to view this content.

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

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.

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?

Please Login or Register  to view this content.

try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

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

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 …

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.

Please Login or Register  to view this content.

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.