Solved: Extracting Data Based on Date Range

[kathyb0527]

I am working on a project where I take the data from one worksheet, put it in another, clean up the data, calculate the days between steps in the process and then chart it. I've accomplished most of it (still need error trapping and some little tweaks), but now managment wants to be able to pull the data for a user specified date range. Meaning, although the data is for a whole year, they might just want the first six months.

Now I'm not sure what my next step is since I see problems with all three ideas I had :
Change how the data is copied and pasted (Already slow, would this slow it down more?)
Filter the data I have (Filtering based on dates can be...unreliable)
Delete rows based on cell values (I know it can be done but I don't know how to do it)

If someone could give me a push in the right direction, it would be greatly appreciated.

I've attached my spreadsheet with associated macros.

[Bob Phillips]

Which date is being referred to?

Filtering on dates is okay, as long as you understand its quirks.

[kathyb0527]

We would be looking at data based on Lab End Date.

[kathyb0527]

I tried filtering the dates, but it doesn't affect the outcome.

[lucas]

To get it down to just being able to filter by the month:

Brute force for column G Actual Lab ending date

[VBA]Option Explicit
Sub Macro4()
Dim cell As Range
Columns("H:H").Insert Shift:=xlToRight
For Each cell In ActiveSheet.Range("G1", ActiveSheet.Range("G" & ActiveSheet.Rows.Count).End(xlUp))
Worksheets("Metric Data").Columns("H").NumberFormat = "mm yyyy"
If cell <> "" Then
cell.Offset(0, 1) = "=" & cell.Address & ""
End If
Next cell

ActiveSheet.Range("H:H").Copy
ActiveSheet.Range("H:H").PasteSpecial Paste:=xlValues
Columns("H:H").Columns.AutoFit

Columns("G:G").Delete Shift:=xlToLeft
End Sub[/VBA]

[kathyb0527]

Hi Lucas,
Thanks for the reply. Unfortunately, I need to calculate the number of days between each step in the process and by changing the date to month and year, I can't get that information.

I think what I would like to do is step through column G and delete rows based on the user input, but I'm not sure how to do that.

[kathyb0527]

Can anyone give me any other suggestions on this problem? Thanks!

[mdmackillop]

Can you repost your attachment?

[kathyb0527]

Thanks, here it is.

[mdmackillop]

Add to a userform with two textboxes ands a button
[VBA]
Private Sub CommandButton1_Click()
For Each cel In Intersect(Columns(7), ActiveSheet.UsedRange)
If cel >= CDate(TextBox1.Text) And cel <= CDate(TextBox2.Text) Then
cel.Offset(, 11) = "x"
End If
Next
ActiveSheet.Columns(18).AutoFilter Field:=1, Criteria1:="<>"
End Sub

Private Sub UserForm_Initialize()
TextBox1.Text = Format(Application.Max(Sheets("Metric Data").Columns(7)) - 180, "dd/mm/yy")
TextBox2.Text = Format(Application.Max(Sheets("Metric Data").Columns(7)), "dd/mm/yy")
End Sub

[/VBA]

[kathyb0527]

I haven't worked with UserForms before so forgive me if these are basic questions.
How do I get the userform to open and close?
Does the Userform_Initialize force the format of the text box?

Thanks!

[Bob Phillips]

I haven't worked with UserForms before so forgive me if these are basic questions.
How do I get the userform to open and close?

In a macro, show it

[vba]

Userform1.Sow
[/vba]

Does the Userform_Initialize force the format of the text box?

Not implicitly, you have to format it.