Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
searching for a time range in Excel
Hi,
I have around 10 thousands rows of data with Date and Time. My plan is to search mark/list out all the the rows with time running from 3am to 6am of the day. What will be the easiest way to do it in VBA? Please advice and thanks in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
searching for a time range in Excel
edvwvw wrote:
Hi, [quoted text clipped - 6 lines] Please advice and thanks in advance. One very quick way would be to conditional format A1 using between 03:00 and 06:00 ( I have presumed that the cells are in time rather than text format) Then click on the cell between A and 1 to highlight the whole sheet, then paste special format The cells that meet the conditional format will be highlighted. If you want it in VBA you could carry out the steps while recording a macro edvwvw If you want it in VBA Sub Times() Set rng = Worksheets(1).[a1] Cells.Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="0.125", Formula2:="0.25" Selection.FormatConditions(1).Interior.ColorIndex = 19 Range("A1").Select End Sub -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
searching for a time range in Excel
On Jun 13, 9:57*am, "edvwvw via OfficeKB.com" <u42512@uwe wrote:
edvwvw wrote: Hi, [quoted text clipped - 6 lines] Please advice and thanks in advance. One very quick way would be to conditional format A1 using between 03:00 and 06:00 ( I have presumed that the cells are in time rather than text format) Then click on the cell between A and 1 to highlight the whole sheet, then paste special format The cells that meet the conditional format will be highlighted. If you want it in VBA you could carry out the steps while recording a macro edvwvw If you want it in VBA Sub Times() Set rng = Worksheets(1).[a1] * * Cells.Select * * Selection.FormatConditions.Delete * * Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ * * * * Formula1:="0.125", Formula2:="0.25" * * Selection.FormatConditions(1).Interior.ColorIndex = 19 * * Range("A1").Select End Sub -- Message posted viahttp://www.officekb.com- Hide quoted text - - Show quoted text - Thank you very much, now I have another question, how do I loop through all these highlighted items, as I have further investigation on these items. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
searching for a time range in Excel
wrote:
Hi, [quoted text clipped - 34 lines] - Show quoted text - Thank you very much, now I have another question, how do I loop through all these highlighted items, as I have further investigation on these items. It depends what you want to do - if you want to make anamenment to the times then the macro will need to be written in a different way. I haven't got time today to look at it for you - maybe someone else will help edveve -- Message posted via http://www.officekb.com |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
searching for a time range in Excel
On Jun 13, 11:31*am, "edvwvw via OfficeKB.com" <u42512@uwe wrote:
wrote: Hi, [quoted text clipped - 34 lines] - Show quoted text - Thank you very much, now I have another question, how do I loop through all these highlighted items, as I have further investigation on these items. It depends what you want to do - if you want to make anamenment to the times then the macro will need to be written in a different way. I haven't got time today to look at it for you - maybe someone else will help edveve -- Message posted viahttp://www.officekb.com Once I filtered all the "times", I then have to look into other columns. But I might still need to look at rows before or after the 3am or 6am frame. So that's why I need to see all rows, but have the ability to loop thru only the filtered rows and refer to other rows before or after the filtered ones |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SEARCHING RANGE | Excel Discussion (Misc queries) | |||
Searching a range with MS Query | Excel Worksheet Functions | |||
searching a range for a sum... | Excel Worksheet Functions | |||
Searching a Range for a number over 40 | Excel Worksheet Functions | |||
3d range searching | Excel Discussion (Misc queries) |