Find a date into ranges.

idnob

New Member
Joined
May 20, 2014
Messages
44
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
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 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
Code:
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
Code:
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
Code:
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
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!
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
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??

Rich (BB 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 that helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,359
Messages
5,528,228
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top