Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

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
0
Jagwarman
Asked:
Jagwarman
  • 8
  • 7
1 Solution
 
Rgonzo1971Commented:
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
 
JagwarmanAuthor Commented:
I guess it is because I am Dim I never know what Dim to include.
0
 
JagwarmanAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rgonzo1971Commented:
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
 
JagwarmanAuthor Commented:
still have problems with my Dim's
0
 
Rgonzo1971Commented:
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
 
JagwarmanAuthor Commented:
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
 
Rgonzo1971Commented:
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
 
JagwarmanAuthor Commented:
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
 
Rgonzo1971Commented:
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
 
JagwarmanAuthor Commented:
Hi Rgonzo1971 I stand corrected, all of thise for feb should also be removed.
0
 
JagwarmanAuthor Commented:
If I am correct it is also removing 31/1/2014 but not removing 23/3/2014

Regards
0
 
Rgonzo1971Commented:
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
 
JagwarmanAuthor Commented:
Hmm! so the fact that it has times in makes a big difference. That worked perfect. Thanks Rgonzo1971. do yuo ever sleep?
0
 
Rgonzo1971Commented:
Sometimes...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now