Solved

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

Posted on 2014-02-13
15
275 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 52

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 52

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 52

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 52

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 52

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 52

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 52

Expert Comment

by:Rgonzo1971
ID: 39858425
Sometimes...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

623 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