+ Reply to Thread
Results 1 to 19 of 19

VBA Macro to delete all rows except current month

  1. #1
    Registered User
    Join Date
    11-06-2019
    Location
    Riyadh
    MS-Off Ver
    Office 10
    Posts
    4

    VBA Macro to delete all rows except current month

    Please help, my sheet column "B" contains date "dd-mmm-yyyy" format and i want to delete all records except current month

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: VBA Macro to delete all rows except current month

    The short advice is - use macro recorder and use autofilter -> fiter aout all values not_current_month and delet all visible rows then swith poff the autofilter.

    Few ammendments to such recorded macro shall make it universal.

    If the above tip is not enough, please read the yellow banner above the post.

    To be more specific:
    Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If trere are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, etc. - please show them all or at least indicate in text) The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

    3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

    To attach an Excel file you have to do the following: Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    11-06-2019
    Location
    Riyadh
    MS-Off Ver
    Office 10
    Posts
    4

    Re: VBA Macro to delete all rows except current month

    Thanks Kaper, sample file attached... sheet 2 shows the desired result... delete all rows except current month in column B
    Attached Files Attached Files

  4. #4
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: VBA Macro to delete all rows except current month

    Try:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    p.s. you've missed 22 Nov on expected results
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  5. #5
    Registered User
    Join Date
    11-06-2019
    Location
    Riyadh
    MS-Off Ver
    Office 10
    Posts
    4

    Re: VBA Macro to delete all rows except current month

    great .. thanks ...
    Last edited by maxi1982; 11-06-2019 at 06:54 AM.

  6. #6
    Registered User
    Join Date
    11-06-2019
    Location
    Riyadh
    MS-Off Ver
    Office 10
    Posts
    4

    Re: VBA Macro to delete all rows except current month

    Quote Originally Posted by maxi1982 View Post
    great .. thanks ...
    hi KOKOSEK... have an issue if we got multiple years in it. the output is giving everything with current month. Nov 17, Nov 18 & Nov 19
    Attached Files Attached Files

  7. #7
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: VBA Macro to delete all rows except current month

    Rather strange as I thought extending koseks's formula to include year as well should fix the year issue i.e.

    Please Login or Register  to view this content.
    but the result was a bit surprising

    month_ck2.jpg

    as you can see that no 11 is year 2017. The values in column F and G a modified macro that extracts month and year values from the B column in case my Scandinavian setting messes up the format but as you can see it reports month 11 and year 2017 but the "CheckMonths" macro don't the delete this row! Strange

    Alf
    Last edited by Alf; 11-07-2019 at 02:54 AM.

  8. #8
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: VBA Macro to delete all rows except current month

    This non-looping macro should work...
    Please Login or Register  to view this content.

  9. #9
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: VBA Macro to delete all rows except current month

    Quote Originally Posted by Alf View Post
    Please Login or Register  to view this content.
    but the result was a bit surprising
    I did not realise that there is 2017 in pos. 11.
    Weird indeed, imho should works but it's not.
    Rick's macro works perfect.

  10. #10
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: VBA Macro to delete all rows except current month

    Weird indeed, imho should works but it's not.
    Yes really weird especially as the checking macro I wrote uses the same function as you did for finding month and year, and this worked without problems giving the right month and year for all values in the B column in the F and G column.

    Please Login or Register  to view this content.
    Alf
    Last edited by Alf; 11-07-2019 at 08:14 AM.

  11. #11
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: VBA Macro to delete all rows except current month

    I perhaps this problem is caused by the OP setting values in the B column as "Custom". The numerical value of 2019-nov-01 is 43770 and numerical value for 2019-nov-30 is 43799 so I modified kokosek's macro using the numerical value instead.

    Please Login or Register  to view this content.
    and now it works as it's supposed to do. I see the OP lives in Riad. I do wonder if they write right to left there? And if this has anything to do with the problem?

    Alf

  12. #12
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: VBA Macro to delete all rows except current month


    not AND Alf not AND.
    OR

    Please Login or Register  to view this content.
    What a shame.....

  13. #13
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: VBA Macro to delete all rows except current month

    Quote Originally Posted by KOKOSEK View Post
    Please Login or Register  to view this content.
    Two points about your code...

    1) Probably a force of habit on your part, but unlike functions in Excel formulas, functions with no arguments in VBA do not require the empty set of parentheses affixed to them. So instead of writing Now() in your code, you could have just written Now and saved some keystrokes.

    2) You could employ the concept used in the code I posted in Message #8 and shorten your If statement (also doing away with the AND/OR problem at the same time) like this...
    Please Login or Register  to view this content.


    Quote Originally Posted by KOKOSEK View Post
    What a shame.....
    I am not understanding... what were you referring to when you said "What a shame"?
    Last edited by Rick Rothstein; 11-07-2019 at 01:03 PM.

  14. #14
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: VBA Macro to delete all rows except current month

    Ad 1. As you've said (bad) habit.
    Ad 2. I said in post #9 that your code works perfect (but I was (with Alf as we see) still thinking why 1st or modified by Alf code does not working properly)
    Ad 3. What a shame was about my thinking, how I can get this AND intead of OR.

  15. #15
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: VBA Macro to delete all rows except current month

    Oh well you found the root of the problem whereas I had the most fanciful and wrong ideas about the cause of the problem

    Well my excuse is that I'm old and Norwegian and according to my Scandinavian neighbours the Swedes and the Danes we Norwegian are the dumbest of all the Scandinavians.

    Alf

  16. #16
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: VBA Macro to delete all rows except current month

    I am not sure who is older Alf
    Anyway, solved. And we've learnt something.

  17. #17
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: VBA Macro to delete all rows except current month

    Quote Originally Posted by KOKOSEK View Post
    I am not sure who is older Alf
    Maybe it's me... I am more than halfway between 70 and 75 years old.

  18. #18
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: VBA Macro to delete all rows except current month

    Then I guess I'm a tad older than you Rick as my 77th birthday will be mid January next year. Perhaps then it will be time to change my moniker to "The Oldy from Mouldy"

    Yes kokosek we / I did learn the difference between AND / OR.

    Alf

  19. #19
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: VBA Macro to delete all rows except current month

    I am 49. Let's finish this thread it goes a bit off topic and mods will get mad.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA to delete number of sheets based on days in current month
    By benjhardie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-09-2019, 03:45 PM
  2. [SOLVED] Macro to extract Rows Colored with Yellow Fill and create a new sheet as per current month
    By prkhan56 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-10-2016, 04:54 AM
  3. Replies: 7
    Last Post: 05-26-2016, 01:49 PM
  4. [SOLVED] Macro to cut and paste current month sheet into the relevant month chosen by a drop down
    By scotty22 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-06-2015, 09:07 AM
  5. [SOLVED] Set PivotTable Filter to Current Day, Current Week, Current Month, or Current Year
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2014, 08:31 AM
  6. Macro using vlookups comparing 2 months paysheet(previous month and current month)
    By srinivasan1965 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-07-2012, 03:45 AM
  7. Delete current month using AUTOFILTER
    By Mslady in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-03-2005, 03:25 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1