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
- 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
- I find in wich column is the starting date
- I find in which column is the ending date
- I (try to) clean the rows that are outside the range given from the user
Hope I was clear, for any questions please feel free to ask.
Thanks in advance to everybody!
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
Code:
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 ask the START DATE and the END DATE from input box
Code:
startdate = InputBox("Start Date date DD-MM-YYYY")
enddate = InputBox("End Date date DD-MM-YYYY")
Code:
For j = 1 To lastcolumn
If ws.Cells(1, j).Value = "INITIAL" Then
initialdate = j + 1
initialtime = j + 2
End If
Next j
Code:
For j = 1 To lastcolumn
If ws.Cells(1, j).Value = "COMPLETE" Then
completedate = j + 1
End If
Next j
Code:
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!