Please help, my sheet column "B" contains date "dd-mmm-yyyy" format and i want to delete all records 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
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
Thanks Kaper, sample file attached... sheet 2 shows the desired result... delete all rows except current month in column B
Try:
Formula: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.
great .. thanks ...
Last edited by maxi1982; 11-06-2019 at 06:54 AM.
Rather strange as I thought extending koseks's formula to include year as well should fix the year issue i.e.
but the result was a bit surprisingPlease Login or Register to view this content.
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.
This non-looping macro should work...
Please Login or Register to view this content.
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.Weird indeed, imho should works but it's not.
AlfPlease Login or Register to view this content.
Last edited by Alf; 11-07-2019 at 08:14 AM.
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.
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?Please Login or Register to view this content.
Alf
not AND Alf not AND.
OR
What a shame.....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.
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.
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.
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
I am not sure who is older Alf
Anyway, solved. And we've learnt something.
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
I am 49. Let's finish this thread it goes a bit off topic and mods will get mad.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks