Find a Value then delete cells before and after

[Ray]

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.

 

[Bernie Deitrick]

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]

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]

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]

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