Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows based on condition | Excel Discussion (Misc queries) | |||
Delete Rows based on value | Excel Worksheet Functions | |||
Delete rows based on criteria | Excel Discussion (Misc queries) | |||
Delete rows based on certain criteria | Excel Discussion (Misc queries) | |||
Delete rows listed less than 8 times??? | Excel Worksheet Functions |