test column if cell is within date range, if not delete whole line

[holilula]

Dear All,

I am trying to find a solution for the following problem since hours and somehow and just can´t get through it.

So what I want to do with my VBA code is to check wether all the dates in column A (beginning with A2 as A1 is the header) is within a specific given date range. The range is mentioned in to cells. let me say from:(P1) to: (Q1)

The dates in cells P1 and Q1 both come from formulas which are settled in the background. e.g. in P1 =today() and Q1=WENN(WOCHENTAG(P1;2)=7;P1-2;WENN(WOCHENTAG(P1;2)=1;P1-3;P1-1))

If a cell in column A has a date which is not in the given dater range, the whole line should be deleted.

I am looking since quiet a while now. I had a code working very slow due to an if-loop-structured-code.

I have heard in other forum that a autofilter function would work faster.

Can anyone help me out with this big (probably only for me) issue.

Thanks and Regards,
holilua

[mdmackillop]

Can you please post a sample workbook Go Advanced/Manage Attachments

[holilula]

Good Morning!

Of course. The attached file is a german file. In sheet Tabelle1 you can see list of 200 lines with data. This sheet has a connection to another file which provides the data. Its a linkage between two files.

If was researching the whole yesterday and got to know that an issue could be the german date-format. some people said that vba, especially for such a requirement needs american-date-format.

However, could turn this information into proper code.

Can anyone help me??

Greetings
holilua

[holilula]

In the Attached file in sheet Tabelle1 Colum A should be checked if the dates there are in the daterange from cell P1 and Q1 or not. if not, the whole line should be deleted.

I reasearched some more got to know that for example for a advanced filter the date format has to be in american date: so MM/DD/YYYY and not DD/MM/YYYY which is normally in german excel version.

to be honest i am stumped for an answer...:dunno

Regards
holilua

[mdmackillop]

Sub Test()
Dim r As Range, x As Range
Set r = ActiveSheet.Cells(1, 1).CurrentRegion
Cells(1, 1).EntireColumn.Insert
Set x = r.Columns(1).Offset(, -1)
r.AutoFilter Field:=1, Criteria1:=">=" & CDbl(Cells(1, "R")), Operator:=xlAnd, _
Criteria2:="<=" & CDbl(Cells(1, "Q"))
x.SpecialCells(xlCellTypeVisible).Value = "x"
r.AutoFilter
x.AutoFilter Field:=1, Criteria1:="="
x.Offset(1).EntireRow.Delete
Columns(1).Delete
End Sub

[holilula]

Hey mdmackillop!

first of all, thank you for your efford!!!

Just tried to insert the code in the commandbutton, but everything automaitcally gets deleted. Is there something special I need to remeber while pasting it in?

[mdmackillop]

There are no dates in Column A which fall between P1 and Q1 in your sample

[holilula]

There are no dates in Column A which fall between P1 and Q1 in your sample

Oh yes you are right.

But for example if I take the dates 2nd may 2017 to 3rd may 2017 (where is known that data exists), somehow it also deletes everything.

I made screenshot, check here:

19429

could the problem be that "older" date is in column "Q" and the "younger" date is "R" ??

[mdmackillop]

Here's my copy of your workbook

[holilula]

Here's my copy of your workbook

Hey mdmackillop!

Thank you for your help, everything works out!

Have a nice day.
Holilula