1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter column grater than 7 days ageing

Discussion in 'VBA Macros' started by Vijayarc, Aug 2, 2018.

  1. Vijayarc

    Vijayarc Member

    Messages:
    67
    Hi Ninja

    I want macro to filter column which is grater than 7 days ageing , trick part is
    columns value will be like, mixed up

    Completed - 14 Days
    Completed - 0 Days
    Rejected - 84 Days
    Rejected - 103 Days
    Completed - 0 Days
    Rejected - 84 Days
    WIP - 601 Days
    NotStarted - 684 Days
    NotStarted
    NotStarted - 84 Days

    I want to filter only WIP-601 days 'NotStarted', NotStarted - 684 Days
    (all days grater than 7 days only)

    please find my sample file in 'M , N 'column I need to filter
    kindly help and do needful

    Attached Files:

  2. vletm

    vletm Excel Ninja

    Messages:
    4,298
    Vijayarc
    I want to filter only WIP-601 days 'NotStarted', NotStarted - 684 Daysfrom which file and from which column
    would find those WIP-601 days ...
    which You would like to filter?
    Could You show some samples?
  3. Vijayarc

    Vijayarc Member

    Messages:
    67
    Hi Vketm

    please find attached screenshot, in my Batch_Report sheet file above In that data has
    Not-started -2 days, Not started-6 days should not filter, its less than 7 days

    I want to filter data grater than 7 days only
    filter should done in 'M and N column
    Code (vb):

      Selection.AutoFilter
      Columns("Z:BC").Select
      Selection.Delete Shift:=xlToLeft
    Selection.AutoFilter
      ActiveSheet.Range("A1:Q1").AutoFilter Field:=9, Criteria1:=Array( _
      "WIP - LOB", "Rejected"), Operator:=xlFilterValues

    ' filter M , N columns ageing grater than 7 days
    In my code scree shot , I done wildcard search filter, but I show all data less then 0 ,2, 6 days also,
    please find below another screen shot were filter code should come

    kindly help and do needful.

    Attached Files:

    Last edited: Aug 2, 2018
  4. vletm

    vletm Excel Ninja

    Messages:
    4,298
    Vijayarc
    Your criteria seems vary ...
    [ Do It ] filters ...
    >> i-column: 'WIP - LOB' or 'Rejected'
    and
    >> m&n-columns: 'NotStarted * Days > 7'

    Attached Files:

  5. Vijayarc

    Vijayarc Member

    Messages:
    67
    HI Vletm

    Big sorry for confusion !!!! thanks for above try,
    I am doing as
    when we click upload Dashboard button,, and upload the db file and then based on macro and filters for each sheets modules, Daily_Prod, Post_Rel , Saui_Ofac,
    filtration will done and pasted on each file and later I will email it on each button click.
    .
    on your file, u done filter on Batch Report itself, but I need M and N filter only
    on Daily_Pro sheet .
    different column filters for Saudi_Ofac sheet and others,

    the code u given should come on module 1 for Daily_Prod sheet as per attached screen, but I am getting error

    please help and do needful, sorry for confusion once again!!
    Capture.PNG
  6. vletm

    vletm Excel Ninja

    Messages:
    4,298
    Vijayarc ... hmm?
    ... and next,
    You would like to fly to the moon?

    Now You start writing something 'when we click upload Dashboard button' - Yes!
    Did You explain that clear before?
    (#1)
    I want macro to filter column which is grater than 7 days ageing , trick part is
    columns value will be like, mixed up
    Completed - 14 Days
    Completed - 0 Days
    Rejected - 84 Days
    Rejected - 103 Days
    Completed - 0 Days
    Rejected - 84 Days
    WIP - 601 Days
    NotStarted - 684 Days
    NotStarted
    NotStarted - 84 Days
    I want to filter only WIP-601 days 'NotStarted', NotStarted - 684 Days
    (all days grater than 7 days only)
    please find my sample file in 'M , N 'column I need to filter

    (#3)
    in my Batch_Report sheet file above In that data has
    Not-started -2 days, Not started-6 days should not filter, its less than 7 days
    I want to filter data grater than 7 days only
    filter should done in 'M and N column

    Cannot You copy or move that my done macro ... somewhere?
    It should work with other sheet ... if structure of sheet is same.
    Why it should belong to Module1?
    Hint ... if You copy paste 'my code' into other code,
    then You should know,
    what 'my code' do and what need to modify!
    I won't do that myself!
    ... and that screenshot ... no way! I've none positive comment!
    That would be more than miracle, if it would start working!

    What would mean Your 'do needful'?
  7. Vijayarc

    Vijayarc Member

    Messages:
    67
    the code is not working as per my request
    my condition is
    I - columns should filter 'WIP - LOB' and 'Rejected'
    M. N column should filter on TS and Credit header, i want to filter the condition as: 'NotStarted, Notstarted - 68 days' (all days should be grater then 7 days)
    'Notstarted - 2 days' - should not filter, because it less than 7 days
    Notstarted - 0 days' - should not filter, because it less than 7 days
    Notstarted - 6 days' - should not filter, because it less than 7 days
    Notstarted - 7 days' - should filter, because it equal to 7 days

    please help and advise how to filter for above condition
    i want the code should place on module 1- below the filter condition code as per image for my understanding, the same code i wan to respect for other sheets and columns
  8. vletm

    vletm Excel Ninja

    Messages:
    4,298
    Vijayarc
    1) Which is not working?
    2) Have You change Your 'requirements' again?
    eg where comes ... TS and Credit header ?
    3) Where is that module 1? And many times asked ... why?
    4) How possible this? ... as per image for my understanding
  9. Vijayarc

    Vijayarc Member

    Messages:
    67
    1)I placed my 1st condition under module 1 , as per image above
    Selection.AutoFilter
    ActiveSheet.Range("A1:Q1").AutoFilter Field:=9, Criteria1:=Array( _
    "WIP - LOB", "Rejected"), Operator:=xlFilterValues

    I request the second condition on module 1

    2).M ,N - Column header is only TS and Credit header
    3)Where is that module 1? And many times asked ... why?
    because if code come in module- for my understanding I will repeated many 20 module , to generate 20 emails.
    4. already I given db file and my master file , -pls check if given code working
    for my conditions

    if code works pls advise how to repeated the same code for all my future 20 sheets like, Post_Rel, Saudi_Ofac ...... to filter same conditions grater than 7 days but different columns
  10. vletm

    vletm Excel Ninja

    Messages:
    4,298
    Vijayarc
    1) If You have not read any of my replies then You would know that Your way won't work!
    2) no answer!
    3) Do You mean this ... I added my the newest version of my code there ...
    Screen Shot 2018-08-06 at 12.51.57.png
    and again no answer to my question
    4) You write something ... but not give any answers ...
    > While using 'my code', the whole code need to use! <
    ps . I gotta use Your snapshot with code ... because not better code from You!
  11. Vijayarc

    Vijayarc Member

    Messages:
    67
    hi vletm

    as per the given code the M column is not filtering as per condition
    I tried all possibilities but only "M" column not filtering .
    please check if done any wrong

    Attached Files:

  12. vletm

    vletm Excel Ninja

    Messages:
    4,298
    Vijayarc
    I've no time to guess how did You test or use that!

    I modified that 'Module1' code
    You have to take care that before run Do_It
    ActiveSheet have to be that sheet which You would like to filter!
    (and that uses AA-column for filtering!)

    M & N-column matter ONLY if 'NotStarted*' otherwise no action!

    I left 'Daily_Pro'-sheet filtered as You have given 'rules'.
    If Your 'rules' has changed ... who knows?

    Attached Files:

  13. Vijayarc

    Vijayarc Member

    Messages:
    67
    Thank u Vletm

    Today I really feels as flying to moon, it worked at last!!!!!

    Thanks you for grate valuable support and for your patience.

    :)
    :awesome::awesome::awesome:thanks again for Chandoo:):)

    Attached Files:

  14. Vijayarc

    Vijayarc Member

    Messages:
    67
    HI Vletm

    I came one issue today as, if condtion not met- that is: if word 'NotStarted"
    not found in filter, the loop going to infinite rows. and am getting only infinite blank rows and excel hangs
    pls check and advise
  15. vletm

    vletm Excel Ninja

    Messages:
    4,298
    Send file here with clear case!
  16. Vijayarc

    Vijayarc Member

    Messages:
    67
    HI Vletm -
    M & N-column matter ONLY if 'NotStarted*' otherwise no action!
    instead of no action
    the line should come as
    " No Case ID is grater than 7 days ageing " in row1 as highlight

    is it possible!!
  17. vletm

    vletm Excel Ninja

    Messages:
    4,298
    Where is that file?
    and
    those challenge 'values'?
  18. Vijayarc

    Vijayarc Member

    Messages:
    67
    Hi Vletm
    please find attached file ,

    the line should come as " No Case ID is found grater than 7 days ageing " in row1 as highlight

    Attached Files:

  19. vletm

    vletm Excel Ninja

    Messages:
    4,298
    Vijayarc
    I didn't figure 'You challenge'!
    You didn't write.. what and where as someone else would guess it!
    1) If You use that code other way as I've written .. it's Your choice!
    I cannot and won't guess how You use it!
    2) I added my version of 'Do_It' to Sheet1 and run it with four sheets.
    Only 'something' is in 'Global Trade'-sheet, there seems to be 23 rows ...
    Maybe Your 'cleaning' won't work 100% ... only You can guess that!
    3) If there are none which match with You named filtering then
    only 1st row will stay visible from 'rows which has data'.
    4) I didn't find any loop nor hangs with 'Do_It'-macro!

    Attached Files:

  20. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    Hi vletm !

    Maybe an idea to avoid any forum abuse for the future next threads :
    if the original post is not clear enough, needing more than a single
    question to ask, do not answer until the thread creator post again
    then just ask for a complete explanation and attachment accordingly.
    If next post still needs questions, ask only a question at a time,
    no more as you yet often saw when several questions are asked,
    few get any answer …
    Propose a solution until all is well defined, crystal clear …
    Maybe for his next thread the author will pay attention as it must
    to create a correct thread and if not, he will enter in the same process :
    waiting - no answer - up - answer just asking a complete explanation
    and an attachment - post asking a question only …
    Like as an education for children …

    As a reminder, any answer is at the level of the initial post :
    the better explanation & attachment, the better & quicker solution !

    As time is on the helpers side …
  21. vletm

    vletm Excel Ninja

    Messages:
    4,298
    Marc L
    Many times, if write more than one line, I'm not sure which one will read!
    .. and after some questions ... someone else would give shorter solution.
    Of course, it's more than possible after digging those answers - details.
    There are many ways to do these and
    I'll do these 'my way' as You have noticed;)
  22. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    No, just post only a question and nothing more until
    you get a valid answer to your question then
    you can create the next question post, as time is on our side …

    Like the Doctor, we are the Time Masters !
  23. Vijayarc

    Vijayarc Member

    Messages:
    67
    hi Vletm
    I required one condition as

    in above we filter grater than 7 days
    in your DO_it macro
    after filter , I want to highlight the rows in red, which is grater than 30 days
  24. vletm

    vletm Excel Ninja

    Messages:
    4,298
    Vijayarc
    What happen to Your previous challenge? (#18 Reply)
  25. Vijayarc

    Vijayarc Member

    Messages:
    67
    my end motto to generate email,, if data not found in filter and sheet is blank,
    In Email generation button I kept message box, 'No transaction meet criteria" as per attached image, as I solved (#18 Reply)

    I want now (reply #23) is on high priority, I tried but no hope , please if you try

    Attached Files:

Share This Page