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.

searching for a time range in Excel
 

wrote:
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.


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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

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

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.

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

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