+ Reply to Thread
Results 1 to 19 of 19

Delete all rows depending on date

  1. 09-01-2022, 02:56 PM #1
    jakjo641
    jakjo641 is offline
    Forum Contributor
    Join Date
    05-29-2022
    Location
    NW England
    MS-Off Ver
    2010
    Posts
    123

    Delete all rows depending on date

    Hello everyone
    I'm looking for the fastest way to Delete all rows depending on date.
    in the results sheet I need to delete all rows that contain a date that is older than from the current month.
    Header in k1, data string starts row2 down ... Thanks in advance!!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-01-2022, 03:54 PM #2
    HSV
    HSV is offline
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    818

    Re: Delete all rows depending on date

    Hello,

    Please Login or Register  to view this content.
    Kind regards, Harry.
    Reply With QuoteRegister To Reply
  3. 09-01-2022, 04:12 PM #3
    jakjo641
    jakjo641 is offline
    Forum Contributor
    Join Date
    05-29-2022
    Location
    NW England
    MS-Off Ver
    2010
    Posts
    123

    Re: Delete all rows depending on date

    Hello, Thanks a lot for your reply
    I tested it on original data but takes way too long.
    Note : the original data is about 20,000 rows so I am searching for faster way for this task ... Thanks again.
    Reply With QuoteRegister To Reply
  4. 09-01-2022, 04:28 PM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,591

    Re: Delete all rows depending on date

    I can't see there being a faster way to select and delete rows. For me, it is immediate.

    Suggest you post a more typical sample of the live workbook.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  5. 09-01-2022, 06:27 PM #5
    jakjo641
    jakjo641 is offline
    Forum Contributor
    Join Date
    05-29-2022
    Location
    NW England
    MS-Off Ver
    2010
    Posts
    123

    Re: Delete all rows depending on date

    Thanks a lot for your reply
    The code works when there is few lines .... but it is taking too much time when there is more rows.
    I am sure that this is possible, but I cannot find how to do this .... Many thanks to anyone who can help with this Thread.
    Last edited by jakjo641; 09-01-2022 at 06:32 PM.
    Reply With QuoteRegister To Reply
  6. 09-01-2022, 06:32 PM #6
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,591

    Re: Delete all rows depending on date

    I suspect there is something else going on. I genuinely do not think there is a faster way.

    Do you have other code in the workbook? Any event handlers? Any complex or volatile formulae?
    Reply With QuoteRegister To Reply
  7. 09-01-2022, 06:44 PM #7
    jakjo641
    jakjo641 is offline
    Forum Contributor
    Join Date
    05-29-2022
    Location
    NW England
    MS-Off Ver
    2010
    Posts
    123

    Re: Delete all rows depending on date

    I don't have other code in the workbook or Any event handlers ... But I have one formula and it's not complicated
    Reply With QuoteRegister To Reply
  8. 09-01-2022, 07:52 PM #8
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,580

    Re: Delete all rows depending on date

    Autofilter is sometime very slow.
    See if this works.
    Col.A must be static serial number, not the result of the formula formla like =Row(A1).
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 09-01-2022, 08:35 PM #9
    beyond Excel
    beyond Excel is offline
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Delete all rows depending on date

    Quote Originally Posted by jakjo641 View Post
    ... but it is taking too much time when there is more rows.
    The following code tested with 36K rows completes in 0.5 seconds:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 09-01-2022, 10:20 PM #10
    jakjo641
    jakjo641 is offline
    Forum Contributor
    Join Date
    05-29-2022
    Location
    NW England
    MS-Off Ver
    2010
    Posts
    123

    Re: Delete all rows depending on date

    Thank you very much Mr beyond Excel for your interest in the issue
    Unfortunately, it takes too long time.
    Reply With QuoteRegister To Reply
  11. 09-01-2022, 10:38 PM #11
    jakjo641
    jakjo641 is offline
    Forum Contributor
    Join Date
    05-29-2022
    Location
    NW England
    MS-Off Ver
    2010
    Posts
    123

    Re: Delete all rows depending on date

    @jindon Nice to see you again ... That is SO clever and amazing! Simple, very fast
    your code worked perfectly. I am still trying to learn it and may be back for a question or two ... Again, thank you.
    Reply With QuoteRegister To Reply
  12. 09-01-2022, 11:03 PM #12
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,937

    Re: Delete all rows depending on date

    Here is an alternate solution using Power Query

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  13. 09-02-2022, 09:53 AM #13
    jakjo641
    jakjo641 is offline
    Forum Contributor
    Join Date
    05-29-2022
    Location
    NW England
    MS-Off Ver
    2010
    Posts
    123

    Re: Delete all rows depending on date

    Thanks a lot for this wonderful information
    Reply With QuoteRegister To Reply
  14. 09-02-2022, 10:11 AM #14
    jakjo641
    jakjo641 is offline
    Forum Contributor
    Join Date
    05-29-2022
    Location
    NW England
    MS-Off Ver
    2010
    Posts
    123

    Re: Delete all rows depending on date

    @jindon
    The last question in this thread

    Please Login or Register  to view this content.
    In the original file, column F contains the names of the customers in alphabetical order and Column D contains the addresses of customers.
    When running the code, the order of the names is different in an unordered manner
    How can Sort names alphabetically from A to Z based on columns F & D
    Thank you for your time spent on coding this up and Thanks a lot for your patience in this issue
    Reply With QuoteRegister To Reply
  15. 09-02-2022, 07:21 PM #15
    jakjo641
    jakjo641 is offline
    Forum Contributor
    Join Date
    05-29-2022
    Location
    NW England
    MS-Off Ver
    2010
    Posts
    123

    Re: Delete all rows depending on date

    Is there any chance for helping me in this point please?
    Reply With QuoteRegister To Reply
  16. 09-02-2022, 09:44 PM #16
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,937

    Re: Delete all rows depending on date

    Did you try the Power Query Solution I provided?
    Reply With QuoteRegister To Reply
  17. 09-02-2022, 10:58 PM #17
    jakjo641
    jakjo641 is offline
    Forum Contributor
    Join Date
    05-29-2022
    Location
    NW England
    MS-Off Ver
    2010
    Posts
    123

    Re: Delete all rows depending on date

    I have no experience in dealing with Power Query ... I will try to study it soon
    Reply With QuoteRegister To Reply
  18. 09-02-2022, 11:11 PM #18
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,580

    Re: Delete all rows depending on date

    If you want to sort "F" & "D" then change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  19. 09-03-2022, 09:30 AM #19
    jakjo641
    jakjo641 is offline
    Forum Contributor
    Join Date
    05-29-2022
    Location
    NW England
    MS-Off Ver
    2010
    Posts
    123

    Re: Delete all rows depending on date

    Thank you so much Jindon ...Your assistance has lightened my work greatly.
    Thanks again for your time. Have a nice day!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Delete rows depending on other row
    By bdgull in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-07-2021, 07:25 PM
  2. Delete rows depending on value
    By Mazzaaa in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-19-2016, 09:16 AM
  3. Delete columns depending on the number of rows.
    By TallOne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-05-2014, 06:42 AM
  4. Delete rows depending on date
    By danieldaniel1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-06-2013, 11:56 AM
  5. delete rows depending on value in two column
    By meslija in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-09-2011, 07:05 AM
  6. macro to delete certain rows depending on value
    By sandbach in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-12-2010, 06:37 AM
  7. [SOLVED] How to delete a set of rows depending on Value
    By msbutton27 in forum Excel General
    Replies: 1
    Last Post: 01-15-2006, 01:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1