Macro to delete certain rows if DATE criteria is met

[lukasz_rz]

Hi Guys.

To describe the problem quickly: I have a worksheet with a huge amount of data. What I need from the macro is to:

1. Delete each row if the cell in this row does not contain specific text (lets say, it is column B, its named "Surname" and the text is "Smith" or the text is "Black"). If the surname is not Smith (or Black), let's delete the whole row and proceed with the next one.

2. When we complete the 1st step, now it is time to check the date. We have column C, named "Date" and of course each of the cell in this column is either blank or contains a date. What I need is to delete each row if the cell (date) in this row is earlier than my date. The thing is, I would like to have a popup window to set up the criteria date each time I run the macro. For example: I put 2014-02-20 in the popup window, and all the rows, where the date in column C is earlier than 20th of Feb 2014 are deleted. Is it possible?

That is more less what I dream about to have. Any help will be highly appreciated.

Thx in advance.

PS.

[ajryan88]

Hi,

Is this the sort of thing you were looking for?

I hope this helps

[Bernie Deitrick]

Insert a userform with a calendar control and a commandbutton, and use code like this in the userform's module:

Please Login or Register  to view this content.

and then in a standard codemodule - run GetDate to start the process

Please Login or Register  to view this content.

[ajryan88]

@Bernie: Nice alternative

[lukasz_rz]

@ajryan88, Bernie.

Thank you both for the tip. I'm pretty sure it works, unfortunately, I do not have the Calendar Control ver. XX installed and I assume I won't have it. Is there another way to set up a date in a macro?

The part of this macro is:

Dim LastRow As Long, r As Long
Dim MyVal
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
LastRow = Range("J" & Rows.Count).End(xlUp).Row
For r = LastRow To 2 Step -1
MyVal = Range("J" & r).Value
If MyVal < "27/09/2013" Then Rows(r).EntireRow.Delete
Next r
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Is it possible, without using the calendar, to set up this bold date from my macro each time I run it? It doesn't have to be done that way, I'm just looking for a solution to do the "date trick" without the calendar...

[ajryan88]

You will have the control, you just need to explicitly tell Excel that you want to use it.

Open the VBE (Alt + F11), double click on UserForm1 in the left-hand pane, then go Tools --> Additional Controls --> Tick "Microsoft Date and Time Picker Control 6.0 (SP4)" then click OK, and you should be right to go

Let me know if you get stuck

[ajryan88]

But if you insist on hard-coding a date into your macro, then here is a solution with 27/09/2013 hard-coded that doesn't require the use of the Date and Time Picker Control.

Note that I haven't deleted the UserForm, so I'm not sure if the code will run without the control added, but the form is never shown, so it should. If it doesn't, let me know and I will remove the form and adjust the code appropriately.

I hope this helps

[lukasz_rz]

@ajryan88, I appreciate your patience while dealing with my numerous questions

Back to the topic.

1. Opening VBE and following your tips is not working, as I simply do not have date and time picker control option where it should be

2. That is why I would suggest to make it without it. Although, is it possible (one more gentle request from my side) to make this macro visible so I could adjust it (meaning by changing the date and/or surnames etc). Now I cannot find it and see how it is written.

3. One more little issue: I need to delete all the rows EXCEPT the mentioned surnames and then earlier than the selected date.

Thanks a lot in advance once more!

[Bernie Deitrick]

This is how mine would work:

Please Login or Register  to view this content.

And this is how to modify your code:

Please Login or Register  to view this content.

[ajryan88]

Whoops on the Surname blunder, sorry about that one. Oh well, easily fixed

About making the date visible...the hard-coded date is currently in the "Sheet1" Worksheet module, but you could place it in a cell instead (see the orange cell above the button).

Let me know

[lukasz_rz]

@Bernie: your proposal seems to be working except one fact. After I modified it a little into:

Please Login or Register  to view this content.

the part with a date selection keeps deleting the first row of my data set, which is the "filter" row with names for each of the columns. All that is left are the results of filtering and deletion. I can find the way to change it.

[Bernie Deitrick]

If there are no cells that meet the criteria, then the first row will be found using the end(xlup), and then will be deleted

Try changing
.End(xlUp)
to
.End(xlUp)(2)
in both places

[lukasz_rz]

No changes. I wonder if the fact that some of the date cells are empty, can affect the whole macro ?

[Bernie Deitrick]

Are your headers in row 1? Can you post a (sanitized if needed) version of your file that shows the error?