Find a date into ranges.

[idnob]

Hi guys,
This is my challange: Remove rows that are not in a time range defined by user.

Details: The sheets is a log of translations, inside it a part from a lot of unuseful data there are 2 columns that i need, the translation starting date and the translation ending date (i set both to date, i.e. dd-mm-yyyy). for example the translation started on 12-03-2014 and finished 15-04-2014.

What I have done so far:
- I defined variables

Dim startdate, enddate As Date
Dim lastrow, lastcolumn, i, j As Long
Dim startdatenumber, enddatenumber As Long
Dim initialdate, initialtime, finaldate, finaltime, completedate, completetime, c As Integer
Dim ws As Worksheet

- i have an excel sheets which has in some columns a date field (dd-mm-yyyy) set by myself
- i ask the START DATE and the END DATE from input box

startdate = InputBox("Start Date date DD-MM-YYYY")
enddate = InputBox("End Date date DD-MM-YYYY")

- I find in wich column is the starting date

For j = 1 To lastcolumn
    If ws.Cells(1, j).Value = "INITIAL" Then
    initialdate = j + 1
    initialtime = j + 2
    End If
Next j

- I find in which column is the ending date

For j = 1 To lastcolumn
    If ws.Cells(1, j).Value = "COMPLETE" Then
    completedate = j + 1
    End If
Next j

- I (try to) clean the rows that are outside the range given from the user

For i = 1 To lastrow
    For j = 1 To lascolumn
       
       If (ws.Cells(i, initialdate).Value < startdate And ws.Cells(i, initialdate).Value > enddate) Then
        ws.Rows(i).Delete
        c = c + 1
       End If
    Next j
Next i

Hope I was clear, for any questions please feel free to ask.
Thanks in advance to everybody!

 

[Snakehips]

idnob,

I assume that the above are just snippets from your code and that in the earlier codes you have actually set the variables 'last column' 'initial date' and 'initial time' in code that you have not posted?

Assuming you have then it could be the mistyped variable 'lascolumn' (should be 'lastcolumn' ??) that is causing your last loop to fail??

For I = 1 To lastrow
    For j = 1 To lascolumn
       
       If (ws.Cells(I, initialdate).Value < startdate And ws.Cells(I, initialdate).Value > enddate) Then
        ws.Rows(I).Delete
        c = c + 1
       End If
    Next j
Next I

Hope that helps.

 

[idnob]

Indeed.
Thanks!