Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - delete the remainder of the text in the cell after my Find | Excel Programming | |||
Find and delete Both Dupblicate Cells in a list | New Users to Excel | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
Find and Delete Blank Cells Formatted as Text | Excel Programming |