Hi all,

I have a problem in removing certain rows in a file.
I have added the file with some information and color coded to wich has to be removed.
Some info: The data in the example is how i get it. I cant adjust it in the original file. I can play around with it by adding columns in wich i trim and such but i didnt get any results for the moment.

The thing it has to do is to delete the rows in wich the Time is after 17:59 and the date equals today - 1 (today = 31/10/2006 - 1 = 30/10/2006).

Thx for reading and the help in advance,

Stefan

The sort order cant be changed because the structure from the file is that it is taken from a txt file. Then certain data is trimmed out and then sorted on a perticular value we need to proceed to the next part of the whole proces.

The rows depend but its never too high. Lets say a maximum of 200 rows.

How do i do that? :shame:

Edit: Fine now, it all works.

Can you mark the thread as a solved please?

Sub test()
For Each Cell In Range("A1", Range("A1").End(xlDown))
If Left(WorksheetFunction.Trim(Cell), 2) > 17 Then
If WorksheetFunction.Trim(Cell.Offset(0, 1)) < Date Then
Cell.Offset(0, 2) = "X"
End If
End If
Next Cell

Columns(3).SpecialCells(xlCellTypeConstants, 23).EntireRow.Delete
End Sub

He does it like i wanted except that if the last row is date - 1 he doesnt do it.

If you put in my example everything in the correct columns and then change the last one or two rows of date to 30/10/2006 he doesnt remove the last one?

Referring to the code of OBP :-)

Tested the code of bomb and that one works. Im gonna test it now on the original file to see how it goes. Ill let you know something in a few.

I tried the trim thing, but the result is that it doesnt look at the cell in time format then.
When i trim it the space is gone but it sillt isnt formatted as time. Thats why i posted it cause i couldnt find a solution for that problem. When i trimmed the space was gone and the cell looked like hour but it wasnt formatted like it.

Can you tell me if the data is in the actual columns as shown?

The data is as shown, i just copyd a part of the column from the original file to show an example. If you wanna know about the column name, its different, the columns in the orignal file are Time = column R and Date = column S with the name of the column on the first row.

Can you tell me the last rightmost column of your data as the VBA will be much quicker if the data is sorted by date and then time before the deletions are made?

The last column on the original file is S, the date column. The data isnt sorted by date and hour, it sorted by another value. That cant be changed also.

The format doesn't make it a date or time see the code above where it is set to a date variable.

Little update:

The code of OBP works now also on the last row and the code of bomb works but removes also the dates below 30/10/2006. My bad for not giving enough information cause it only has to delete the date -1, so today only 30/10/2006.

Thx alot for the help guys, in case of further problems ill get back to you

But for now all is ok, thx.

Works on mine?