Solved

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

Posted on 2014-02-13
15
264 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

809 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