VBA -Delete data not in a given date range

Question

am attempting to remove data from a worksheet based on a given date range. On sheet "Grower Reporting" I have a small data set with dates in column L. The rest of the data are in columns N, M and O. In the same worksheet I have two cells that define the date range, cell "A2"=Start Date and cell "B2"= End Date. I am attempting to remove from my dataset any date & adjacent data that does not fall into the given date range.

Thanks to ARich and varocarbas for helping me get this far!

Here is what I have done (bear with me, I have a tenuous grasp of coding):

Sub delete_data()

Dim startDate As Date
Dim endDate As Date
Dim r_date As Date


startDate = Sheets("Grower Reporting").Range("a2").Value
endDate = Sheets("Grower Reporting").Range("b2").Value

Set r = Sheets("Grower Reporting").Range("L:L").Find(r_date, Range("L2"), _
LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext)

If (r_date >= startDate And r_date <= endDate) Then

  r.Offset(0, -1).Value = " "

     End If

End Sub

The above code doesn't seem to do anything. I think I am on the right track but am in dire need of some guidance.

Is there a way? Sure. But what have you tried? I looked at your previous question and this code is identical to the marked answer there. You know... you are expected to do something by your own. What problem (getting the dates, comparing the dates, etc.) has you tried to address and why you weren't able to fix it? Also it is not clear what you mean with "returning" as far as your code does not return anything, just modify some cells. — user2480047
– user2480047, Commented Oct 7, 2013 at 17:41
I have tried defining two dates as variables but could not figure out the code compare the date range with the dates in column "I". — NewSpeaker
– NewSpeaker, Commented Oct 7, 2013 at 18:26
The code you have posted is not relevant for this problem (and was written by someone else); having it as a support is fine, but it does not prove your minimal understanding, attempted solutions, etc. Write the bits dealing with dates and, please, clarify what you meant with the data you want to return. — user2480047
– user2480047, Commented Oct 7, 2013 at 18:28
Also, sorry for the unclear terminology. I am a bit of a novice when it comes to programming. — NewSpeaker
– NewSpeaker, Commented Oct 7, 2013 at 18:34
I don't have any problem with the terminology as far as it is clear. "Return" implies a function where certain analysis is performed and certain value(s) (variable) is sent back to the caller. If you didn't mean that, just clarify what you want: "adding values to the cells as in my original code for the rows where the date's conditions are met"? -> not sure. The more detailed are your explanations the better. Please, write down what you have tried on the dates front and confirm what you want to do exactly. — user2480047
– user2480047, Commented Oct 7, 2013 at 18:40

user2480047 · Accepted Answer · 2013-10-07 19:09:55Z

1

You have to perform the checks inside the loop (for each row). Dates accept the same operators than numbers. Sample code:

If Not r Is Nothing Then
    Dim curDate As Date
    firstAddress = r.Address
    Do
        curDate = Sheets("Grower Reporting").Cells(i, 9).Value 'Column I
        If (curDate >= d1 And curDate <= d2) Then
           Sheets("Grower Reporting").Cells(i, 13).Value = r.Value
           Sheets("Grower Reporting").Cells(i, 14).Value = r.Offset(0, -3).Value
           Sheets("Grower Reporting").Cells(i, 15).Value = r.Offset(0, 6).Value
           i = i + 1
           Set r = Sheets("Grower Rejection Data").Range("E:E").FindNext(r)
        End If
    Loop While Not r Is Nothing And r.Address <> firstAddress
End If

NOTE: this code assumes that all the inputs have the same (date) format. If this is not the case, you might have to rely on the Format function or affect the Cell properties. In case of dealing with different "Regional Settings" (e.g., computer in English from the UK and data from the US; where the date formats are different), you might get involved in some problematic issues.

answered Oct 7, 2013 at 19:09

user2480047

Sign up to request clarification or add additional context in comments.

12 Comments

NewSpeaker Over a year ago

This looks exactly like what I was looking for! I appreciate all of your help!

user2480047 Over a year ago

@user1437463 no problem. I hope that will help you to keep learning VBA and know how to face the problems.

NewSpeaker Over a year ago

I'm getting an error "Run-Time Error 91 Object variable or With Block variable not set". The error is occurring on line 13 of the above code. Should I paste what I have in my original question?

user2480047 Over a year ago

@user1437463 Better tell me the exact line. If your previous code was working, this version has also to work.

user2480047 Over a year ago

@user1437463 you missed the condition. But it is the same... the bits I introduced do not provoke any error by assuming right inputs. This code assumes that the "I" column contains dates. In any case, the error you report does not make any sense... See, let's speed up this process. Do you mind to upload the whole spreadsheet such that I can take a look at it and tell you what is wrong?

|

Collectives™ on Stack Overflow

VBA -Delete data not in a given date range

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related