Find a Value then delete cells before and after
 

I've searched through most of the strings in the forum, but I cannot find the
exact help I need. What I need my code to do is use a value that's found in
cell K6 (which is a time in format h:mm:ss, and will change) and find the
same time value in column B. Then delete all the data that occurs before
that value. I do not want to delete the rows, just the cells. I use the
code,
FinalRow = Cells(Rows.Count, "K").End(xlUp).Row
To find the last time value in column K. I want to use the time value that
occurs at the end of column K to find the same value in Column B. Only this
time I want to delete all the data that occurs after that value. Again, the
cells, not the entire row.

Thankd for your help.

Find a Value then delete cells before and after
 

Ray,

I've assumed that you have true times, not strings....

Sub ClearTimes()
Dim myC As Range
Dim T1 As Date
Dim T2 As Date
T1 = Range("K6").Value
T2 = Cells(Rows.Count, "K").End(xlUp).Value
For Each myC In Intersect(Range("B:B"), ActiveSheet.UsedRange)
If myC.Value < T1 Or myC.Value T2 Then myC.ClearContents
Next myC
End Sub

HTH,
Bernie
MS Excel MVP



"Ray" wrote in message
...
I've searched through most of the strings in the forum, but I cannot find
the
exact help I need. What I need my code to do is use a value that's found
in
cell K6 (which is a time in format h:mm:ss, and will change) and find the
same time value in column B. Then delete all the data that occurs before
that value. I do not want to delete the rows, just the cells. I use the
code,
FinalRow = Cells(Rows.Count, "K").End(xlUp).Row
To find the last time value in column K. I want to use the time value
that
occurs at the end of column K to find the same value in Column B. Only
this
time I want to delete all the data that occurs after that value. Again,
the
cells, not the entire row.

Thankd for your help.

Find a Value then delete cells before and after
 

Thanks for the reply. But it's deleting the whole column of data. When the
macro is done the range of times should be from 21:39:02 to 22:01:00 with
each second being present (these times only apply to the first data set).
Right now, everything is removed. Also, I need it to match the K6 and last
values and remove the cells above and below that range from columns A through
E. For example, if the K6 Time is found in cell B1500, then remove all cells
from A6:E1499 and shift the cells up so the Matched K6 value appears in B6.
Then Match the last time found in column K and delete all the cells that
occur after it in columns A:E.

"Bernie Deitrick" wrote:

Ray,

I've assumed that you have true times, not strings....

Sub ClearTimes()
Dim myC As Range
Dim T1 As Date
Dim T2 As Date
T1 = Range("K6").Value
T2 = Cells(Rows.Count, "K").End(xlUp).Value
For Each myC In Intersect(Range("B:B"), ActiveSheet.UsedRange)
If myC.Value < T1 Or myC.Value T2 Then myC.ClearContents
Next myC
End Sub

HTH,
Bernie
MS Excel MVP



"Ray" wrote in message
...
I've searched through most of the strings in the forum, but I cannot find
the
exact help I need. What I need my code to do is use a value that's found
in
cell K6 (which is a time in format h:mm:ss, and will change) and find the
same time value in column B. Then delete all the data that occurs before
that value. I do not want to delete the rows, just the cells. I use the
code,
FinalRow = Cells(Rows.Count, "K").End(xlUp).Row
To find the last time value in column K. I want to use the time value
that
occurs at the end of column K to find the same value in Column B. Only
this
time I want to delete all the data that occurs after that value. Again,
the
cells, not the entire row.

Thankd for your help.

Find a Value then delete cells before and after
 

Ray,

What are the actual values in column B? Are they Date and Times? (format them for number - are the
values between 0 and 1, or are they values like 39976.25?)
Are the Values in K just times?

Anyway, this one will correct the Date/Time issue....

Sub ClearTimes2()
Dim T1 As Date
Dim T2 As Date
Dim myR As Long
Dim myT As Double

T1 = Range("K6").Value
T2 = Cells(Rows.Count, "K").End(xlUp).Value
For myR = Range("B" & Rows.Count).End(xlUp).Row To 6 Step -1
myT = Cells(myR, 2).Value - Int(Cells(myR, 2).Value)
If myT < CDbl(T1) Or myT CDbl(T2) Then
Cells(myR, 2).Offset(0, -1).Resize(1, 5).Delete Shift:=xlUp
End If
Next myR
End Sub


HTH,
Bernie
MS Excel MVP


"Ray" wrote in message
...
Thanks for the reply. But it's deleting the whole column of data. When the
macro is done the range of times should be from 21:39:02 to 22:01:00 with
each second being present (these times only apply to the first data set).
Right now, everything is removed. Also, I need it to match the K6 and last
values and remove the cells above and below that range from columns A through
E. For example, if the K6 Time is found in cell B1500, then remove all cells
from A6:E1499 and shift the cells up so the Matched K6 value appears in B6.
Then Match the last time found in column K and delete all the cells that
occur after it in columns A:E.

"Bernie Deitrick" wrote:

Ray,

I've assumed that you have true times, not strings....

Sub ClearTimes()
Dim myC As Range
Dim T1 As Date
Dim T2 As Date
T1 = Range("K6").Value
T2 = Cells(Rows.Count, "K").End(xlUp).Value
For Each myC In Intersect(Range("B:B"), ActiveSheet.UsedRange)
If myC.Value < T1 Or myC.Value T2 Then myC.ClearContents
Next myC
End Sub

HTH,
Bernie
MS Excel MVP



"Ray" wrote in message
...
I've searched through most of the strings in the forum, but I cannot find
the
exact help I need. What I need my code to do is use a value that's found
in
cell K6 (which is a time in format h:mm:ss, and will change) and find the
same time value in column B. Then delete all the data that occurs before
that value. I do not want to delete the rows, just the cells. I use the
code,
FinalRow = Cells(Rows.Count, "K").End(xlUp).Row
To find the last time value in column K. I want to use the time value
that
occurs at the end of column K to find the same value in Column B. Only
this
time I want to delete all the data that occurs after that value. Again,
the
cells, not the entire row.

Thankd for your help.

Find a Value then delete cells before and after
 

Bernie,
Thanks for the code, it works great. The values in column B are Date and
Times but I have them formated to show only the time. The values in K come
from a formula to add 4 hours to the time the was recorded when the
measurement occurred. The purpose of all this is to match up the two
different times to make a comparison between the two instruments. The nature
of the beast was that intrument 2 (Column K) I could turn on and off whenever
I wanted and I only needed 20 minutes of data, but the entire process takes
40 minutes to complete. Instrument 1 (Column B) had a set time of 40 minutes
that I could not stop once it started, so I have 20 minutes of data that I
don't need and must exclude from my analysis. FYI; the reason for adding 4
hours was that instrument 1 recorded time in Local and instrument 2 was in
GMT and they were 4 hours apart.
Thanks for your help. You have saved me a great deal of time searching and
matching the timestamps.

Cheers,
-Ray

"Bernie Deitrick" wrote:

Ray,

What are the actual values in column B? Are they Date and Times? (format them for number - are the
values between 0 and 1, or are they values like 39976.25?)
Are the Values in K just times?

Anyway, this one will correct the Date/Time issue....

Sub ClearTimes2()
Dim T1 As Date
Dim T2 As Date
Dim myR As Long
Dim myT As Double

T1 = Range("K6").Value
T2 = Cells(Rows.Count, "K").End(xlUp).Value
For myR = Range("B" & Rows.Count).End(xlUp).Row To 6 Step -1
myT = Cells(myR, 2).Value - Int(Cells(myR, 2).Value)
If myT < CDbl(T1) Or myT CDbl(T2) Then
Cells(myR, 2).Offset(0, -1).Resize(1, 5).Delete Shift:=xlUp
End If
Next myR
End Sub


HTH,
Bernie
MS Excel MVP


"Ray" wrote in message
...
Thanks for the reply. But it's deleting the whole column of data. When the
macro is done the range of times should be from 21:39:02 to 22:01:00 with
each second being present (these times only apply to the first data set).
Right now, everything is removed. Also, I need it to match the K6 and last
values and remove the cells above and below that range from columns A through
E. For example, if the K6 Time is found in cell B1500, then remove all cells
from A6:E1499 and shift the cells up so the Matched K6 value appears in B6.
Then Match the last time found in column K and delete all the cells that
occur after it in columns A:E.

"Bernie Deitrick" wrote:

Ray,

I've assumed that you have true times, not strings....

Sub ClearTimes()
Dim myC As Range
Dim T1 As Date
Dim T2 As Date
T1 = Range("K6").Value
T2 = Cells(Rows.Count, "K").End(xlUp).Value
For Each myC In Intersect(Range("B:B"), ActiveSheet.UsedRange)
If myC.Value < T1 Or myC.Value T2 Then myC.ClearContents
Next myC
End Sub

HTH,
Bernie
MS Excel MVP



"Ray" wrote in message
...
I've searched through most of the strings in the forum, but I cannot find
the
exact help I need. What I need my code to do is use a value that's found
in
cell K6 (which is a time in format h:mm:ss, and will change) and find the
same time value in column B. Then delete all the data that occurs before
that value. I do not want to delete the rows, just the cells. I use the
code,
FinalRow = Cells(Rows.Count, "K").End(xlUp).Row
To find the last time value in column K. I want to use the time value
that
occurs at the end of column K to find the same value in Column B. Only
this
time I want to delete all the data that occurs after that value. Again,
the
cells, not the entire row.

Thankd for your help.