Delete rows based upon a range of times

  • Thread starter Thread starter farmboy
  • Start date Start date
F

farmboy

I have thousands of rows of data with Date & Time data combined in column B.
I do know how to seperate the date and time if need be, however, the program
this data will ultimately be imported into desires the data & time fields
combined. Also note, in case it matters, the date format is dd/mm/yyyy.

I need to DELETE rows based on the certain times. Essentially, I need to
delete all rows EXCEPT those times from 15:30 to 19:10 – all other times are
deleted.

I am in need of the code that could be pasted into a macro to delete these
rows.

I appreciate very much any help as I am not a VB programmer – just learning!!!

THANKS in Advance.
 
try this

Sub DeleteRows()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowCount = 2
'put x is column IV for rows to delete
Do While Range("A" & RowCount) <> ""
If Range("A" & RowCount) < TimeValue("15:30") Or _
Range("A" & RowCount) > TimeValue("19:10") Then

Range("IV" & RowCount) = "X"
End If
RowCount = RowCount + 1
Loop

'autofilter for x's
Columns("IV").AutoFilter
Columns("IV").AutoFilter Field:=1, Criteria1:="X"
Set c = Columns("IV").Find(what:="X", _
LookIn:=xlValues, lookat:=xlWhole)
'check if any daata was found
If Not c Is Nothing Then
'delete visible rows
Rows("2:" & LastRow).Cells _
.SpecialCells(Type:=xlCellTypeVisible).Delete
'remove autofilter
Columns.AutoFilter
End If
 
Thanks Joel but something is wrong as it deleted all rows except Row1. The
time is in military format (23:59) and is seperated from the date by a space.

It appears you were checking the date & time cell in Column A when in
reality it was in B but I corrected that in the worksheet so the date/time
combo data is now in Column A. Retried Macro with same results....deletes
all but row 1.
 
Joel,

Your code worked as intended after I split out the date and time from one
cell. Now I just have to merge the seperated date and times back into one
cell. Seems I have seen something in these discussions about that. Thanks
again!!!
 
Joel,

Difficulties remain merging the date and time cells back into one. Date is
in A1, Time in B2. =A1&B1 yields the date and what appears to be the time in
decimal format depsite the fact the formating for C1 is set to dd/mm/yyyyy
h:mm

Any ideas? You've been MOST helpful thus far. Many thanks.
 
The website isn't sending email responses to me. I didn't see your message
yesterday. sorry.

There are three different solutions.

1) You can just add the date and time together. to get the orignal time you
started with. Excel keep time as follows

a) Day 1 = Jan 1, 1900
b) every day is counted as 1 so 2 = Jan 2, 1900
c) Every hour is 1/24
d) A minute = 1/(24*60)

so Time les than a day is stored as a fraction
12:00 AM = 0
6:00 AM = .25
12:00 PM = .5
6:00 PM = .75

2) You should of left the original data date an added two new columns for
the data and time


3) Modify the macro so you don't have to split the time

Do While Range("A" & RowCount) <> ""
CompareDate = Range("A" & RowCount)
'int function get the integer portion of the date
'comparehours will be the fractional portion of the date
ComparHours = CompareDate - Int(CompareDate)
If ComparHours < TimeValue("15:30") Or _
ComparHours > TimeValue("19:10") Then

Range("IV" & RowCount) = "X"
End If
RowCount = RowCount + 1
Loop
 
Back
Top