Solved

Remove any items with a date that are greater than last day of last month

Posted on 2014-02-13
15
261 Views
Last Modified: 2014-02-14
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
0
Comment
Question by:Jagwarman
  • 8
  • 7
15 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39855622
Hi,

pls try
Sub Macro()
For Each c In Range(Range("G2"), Range("G" & Rows.Count).End(xlUp))
    If c.Value > Application.WorksheetFunction.EoMonth(Date, -1) Then
        c.EntireRow.Delete
    End If
Next
End Sub

Open in new window

EDIT Greater not GreaterorEqual

Regards
0
 

Author Comment

by:Jagwarman
ID: 39855792
I guess it is because I am Dim I never know what Dim to include.
0
 

Author Comment

by:Jagwarman
ID: 39855809
I set it as Range....

So If c.Value >= Application.WorksheetFunction.EoMonth(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.WorksheetFunction.EoMonth(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
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39855838
Hi,

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

Open in new window

Regards
0
 

Author Comment

by:Jagwarman
ID: 39855854
still have problems with my Dim's
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39855995
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 

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 39856465
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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39856621
Hi,

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
0
 

Author Comment

by:Jagwarman
ID: 39857243
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
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39858273
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
0
 

Author Comment

by:Jagwarman
ID: 39858321
Hi Rgonzo1971 I stand corrected, all of thise for feb should also be removed.
0
 

Author Comment

by:Jagwarman
ID: 39858329
If I am correct it is also removing 31/1/2014 but not removing 23/3/2014

Regards
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39858381
Now I saw thart your dates have time as well
So pls try

Sub Macro()

Dim lstRw As Long
Dim Idx As Long
Dim c As Range
Dim WsF As Object

Set WsF = Application.WorksheetFunction
lstRw = Range("G" & Rows.Count).End(xlUp).Row
For Idx = lstRw To 2 Step -1
    Set c = Range("G" & Idx)
    If Int(c) > WsF.EoMonth(Date, -1) Then
        c.EntireRow.Delete
    End If
Next

End Sub

Open in new window

Regards
0
 

Author Comment

by:Jagwarman
ID: 39858398
Hmm! so the fact that it has times in makes a big difference. That worked perfect. Thanks Rgonzo1971. do yuo ever sleep?
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39858425
Sometimes...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now