Solved

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

Posted on 2014-02-13
15
267 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 50

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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 50

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 50

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
 
LVL 50

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 50

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 50

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 50

Expert Comment

by:Rgonzo1971
ID: 39858425
Sometimes...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

838 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