Jagwarman
asked on
Remove any items with a date that are greater than last day of last month
Is this possible.
I need to remove all rows where the date in column 'G' is greater than the last day of the previous month. So if I run my file on or after 1st March all rows where the date in Column 'G' is >= 1st March need to be removed.
Thanks
Regards
I need to remove all rows where the date in column 'G' is greater than the last day of the previous month. So if I run my file on or after 1st March all rows where the date in Column 'G' is >= 1st March need to be removed.
Thanks
Regards
ASKER
I guess it is because I am Dim I never know what Dim to include.
ASKER
I set it as Range....
So If c.Value >= Application.WorksheetFunct ion.EoMont h(Date, -1)
it removes these
03/02/2014
03/02/2014
03/02/2014
31/01/2014
03/02/2014
03/02/2014
and If c.Value > Application.WorksheetFunct ion.EoMont h(Date, -1)
it removes these
03/02/2014
03/02/2014
03/02/2014
31/01/2014
03/02/2014
03/02/2014
so in both cases it is not removing those with dates
23/03/2014
23/03/2014
23/03/2014
????
Thanks
So If c.Value >= Application.WorksheetFunct
it removes these
03/02/2014
03/02/2014
03/02/2014
31/01/2014
03/02/2014
03/02/2014
and If c.Value > Application.WorksheetFunct
it removes these
03/02/2014
03/02/2014
03/02/2014
31/01/2014
03/02/2014
03/02/2014
so in both cases it is not removing those with dates
23/03/2014
23/03/2014
23/03/2014
????
Thanks
Hi,
I forgot we are deleting rows
I forgot we are deleting rows
Sub Macro()
lstRw = Range("G" & Rows.Count).End(xlUp).Row
For Idx = lstRw To 2 Step -1
Set c = Range("G" & Idx)
If c >= Application.WorksheetFunction.EoMonth(Date, -1) Then
c.EntireRow.Delete
End If
Next
End Sub
Regards
ASKER
still have problems with my Dim's
pls try
Sub Macro()
Dim lstRw as Long
Dim Idx as Long
Dim c as Range
lstRw = Range("G" & Rows.Count).End(xlUp).Row
For Idx = lstRw To 2 Step -1
Set c = Range("G" & Idx)
If c >= Application.WorksheetFunction.EoMonth(Date, -1) Then
c.EntireRow.Delete
End If
Next
End Sub
ASKER
Rgonzo1971
I have 356 rows in my test file and 3 of thise have dates >this month i.e. they are dated March so I am looking to remove 3 items.
Your first macro removed 6 items all of which were dated less than this month/year.
Your latest maro removed 43 items that were dated either Jan or Feb 14.
attached for your trial
month.xlsm
I have 356 rows in my test file and 3 of thise have dates >this month i.e. they are dated March so I am looking to remove 3 items.
Your first macro removed 6 items all of which were dated less than this month/year.
Your latest maro removed 43 items that were dated either Jan or Feb 14.
attached for your trial
month.xlsm
Hi,
When you said
I understood if calculated today the 31/01/2014
Do you want to delete greater than the last day of this month => 28/2/2014
Regards
When you said
is greater than the last day of the previous month
I understood if calculated today the 31/01/2014
Do you want to delete greater than the last day of this month => 28/2/2014
Regards
ASKER
if today is 1st or 2nd or 3rd or 4th etc. of march 2014 I need the macro to delete all items dated 1st or 2nd or 3rd or 4th etc of March, April May etc 2014
or as you said if calculated today the 31/01/2014 I want all items for feb, March Apr etc deleted.
I only want to keep item upto and including the last day of last month.
Regards
or as you said if calculated today the 31/01/2014 I want all items for feb, March Apr etc deleted.
I only want to keep item upto and including the last day of last month.
Regards
Hi,
You say you only want the last 3 ones to be deleted
but with your rule it sould delete other rows see example at row 163
Regards
monthV1.xlsm
You say you only want the last 3 ones to be deleted
but with your rule it sould delete other rows see example at row 163
Regards
monthV1.xlsm
ASKER
Hi Rgonzo1971 I stand corrected, all of thise for feb should also be removed.
ASKER
If I am correct it is also removing 31/1/2014 but not removing 23/3/2014
Regards
Regards
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hmm! so the fact that it has times in makes a big difference. That worked perfect. Thanks Rgonzo1971. do yuo ever sleep?
Sometimes...
pls try
Open in new window
EDIT Greater not GreaterorEqualRegards