Delete rows based upon a range of times
 

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.

Delete rows based upon a range of times
 

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

"farmboy" wrote:

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.

Delete rows based upon a range of times
 

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" wrote:

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

"farmboy" wrote:

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.

Delete rows based upon a range of times
 

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!!!

"farmboy" wrote:

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" wrote:

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

"farmboy" wrote:

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.

Delete rows based upon a range of times
 

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.

"farmboy" wrote:

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!!!

"farmboy" wrote:

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" wrote:

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

"farmboy" wrote:

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.

Delete rows based upon a range of times
 

=A1+B1 and format appropriately.
--
David Biddulph

farmboy wrote:
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.

"farmboy" wrote:

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!!!

"farmboy" wrote:

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" wrote:

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

"farmboy" wrote:

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.

Delete rows based upon a range of times
 

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

"farmboy" wrote:

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.

"farmboy" wrote:

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!!!

"farmboy" wrote:

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" wrote:

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

"farmboy" wrote:

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.