Excel VBA - How to Find Last 24 Hours date from Today and Yesterday -

Excel VBA - How to Find Last 24 Hours date from Today and Yesterday -

thanks experts contributing so, help newbie me

i given csv dump , asked automate report

i'm using excel vba

there 16 requirements / steps finish automates study able finish 14 out of them ( of course of study searching in , changing code requirements !!! )

the final steps in macro involve filters, never wrote filters through code , new me

below 1 on requirement, involves filters after lot of searching , debugging, able code below procedure , works fine

------------------------------------------------------------------------------------------ requirement: exclude rows contains fdn column ci grouping name ------------------------------------------------------------------------------------------ dim datarange range dim column_number integer ' find info range in sheet [ working wb ], have select info phone call unknownrange(workbook_02_name, workbook_02_sheet1_name) set datarange = range(cells(firstrow, firstcol), cells(lastrow, lastcol)) ' set reference workbook_02 set workbook_02 = getworkbookreference(workbook_02_path_office & "\" & workbook_02_name) ' worksheet name var workbook_02_sheet1_name = workbook_02.sheets(1).name workbooks(workbook_02_name).worksheets(workbook_02_sheet1_name) workbooks(workbook_02_name).worksheets(workbook_02_sheet1_name).activate ' required column # column name column_number = columnname_to_columnnumber("ci") ' remove datum on column [ ci ] having fdn 'datarange.removeduplicates columns:=column_number, header:=xlyes 'worksheets(workbook_02_sheet1_name).autofiltermode = true datarange datarange.autofilter field:=column_number, criteria1:="=*fdn*" set excluderange = datarange.offset(1, 0).specialcells(xlcelltypevisible) excluderange 'remove datum on column [ ci ] having vdi .entirerow.delete '.delete end worksheets(workbook_02_sheet1_name).autofiltermode = false end end ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ' -------------------------------------------------------- function columnname_to_columnnumber(columnname string) ' -------------------------------------------------------- ' column name column number colname = columnname debug.print range(colname & 1).column columnname_to_columnnumber = range(colname & 1).column ' -------------------------------------------------------- end function ' -------------------------------------------------------- ' ' ------------------------------------------------------------------------------------------

request #1

in above code i'm using .activate activate worksheet in read have avoid ( .select / .activate .... ) workbook i'm using has single sheet, can avoid .activate in above code

however, when work on multiple sheets, if not utilize .activate, sheet need work, not focus i'm not sure how avoid .activate

------------------------------------------------------------------------------------------ requirement: having lastly 24 hours date ( yesterday 9 pm today 9 pm) column f "time stamp" ------------------------------------------------------------------------------------------

column f have time stamps supposed filter out lastly 24 hours column

here sample of column f

9/15/2014 7:33 9/15/2014 7:24 9/15/2014 7:21 9/15/2014 7:20 9/15/2014 6:43 9/15/2014 6:32 9/15/2014 6:25 9/15/2014 5:43 9/15/2014 5:30 9/15/2014 5:27 9/15/2014 4:56 9/15/2014 4:41 9/15/2014 4:28 9/15/2014 3:29 9/15/2014 3:29 9/15/2014 2:26 9/29/2014 19:22 9/29/2014 18:47 9/29/2014 18:42 9/29/2014 18:20 9/29/2014 18:05 9/29/2014 17:40 9/29/2014 17:36 9/29/2014 16:54 10/15/2014 10:07 10/15/2014 10:07 10/15/2014 10:07 10/15/2014 10:07 10/15/2014 10:07 10/15/2014 10:07

i have no clue how code this, thinking in next steps:

step #1 find year

step #2 have find out todays date , yesterdays date, assign them #2 variables

step #3 basing on todays date, have find out how many hours in filter if there 24 entries done, have got 24 hrs , status [ yesterday 9 pm today 9 pm ]

step #4 basing on todays date, if there less 24 entries, have yesterdays date , start couting till completes 24 hrs

(or)

avoid filters , utilize pure vba acomplish above #4 steps on column f

request #2

please help me out find 24 hours date, using filters or other means

------------------------------------------------------------------------------------------ below recorded macro ------------------------------------------------------------------------------------------ sub last_24_hours_() ' ' last_24_hours_ macro ' last_24_hours_ ' ' range("a1").select selection.autofilter application.goto reference:="r1c6" activesheet.range("$a$1:$cm$38854").autofilter field:=6, operator:= _ xlfiltervalues, criteria2:=array(3, "10/14/2014 10:59:48", 3, _ "10/14/2014 11:57:37", 3, "10/14/2014 12:58:39", 3, "10/14/2014 13:59:39", 3, _ "10/14/2014 14:59:39", 3, "10/14/2014 15:59:42", 3, "10/14/2014 16:59:44", 3, _ "10/14/2014 17:59:43", 3, "10/14/2014 18:59:44", 3, "10/14/2014 19:59:46", 3, _ "10/14/2014 20:59:47", 3, "10/14/2014 21:59:49", 3, "10/14/2014 22:59:51", 3, _ "10/14/2014 23:59:52", 2, "10/15/2014") end sub ------------------------------------------------------------------------------------------

thanks in advance,

chakri

9:08 11/9/2014 jeeped

sorry nor beingness clear. yes there dates before now().

please note csv dump have contains info till 15-oct-2014 only.

here manual way do, collect 24 hours column f:

(1) apply autofilter.

(2) set filter on column f

(3) filter select year 2014, under have #2 months available [ sep , oct ]

(4) in current dump have, oct-15-2014 has 11 entries [ i.e, 11 hours ], select [ 00, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10 ]

(5) so, got 10 hours , have remaining 24 - 11 = 13 hours,

(6) remaining 13 hours, select, oct-14-2014, leave first 11 hours ( have them oct-15-2014 ), , start selecting values [ 11, 12 ,13, 14 ,15, 16, 17, 18, 19, 20, 21, 22, 23 ]

so, above manual procedure do, now() might not helpful ( on current csv dump have, max dates oct-15 , oct-14 )

however, code provided helpful latest dumps get

so, how should above manual procedure coded 24 hours oct-15 , oct-14

also, in code, not sure if time factor [ ( yesterday 9 pm today 9 pm) ] followed or not

thanks chakri

this short snippet may plenty started .autofilter property.

dim rfilteredcells range, lcountfilteredcells long if activesheet.autofiltermode activesheet.autofiltermode = false activesheet.cells(1, 1).currentregion .autofilter .autofilter field:=6, criteria1:= _ ">=" & (now - 1), operator:=xland, criteria2:="<=" & 'get count of visible cells lcountfilteredcells = application.subtotal(102, .columns("f")) debug.print lcountfilteredcells 'set range object filtered cells set rfilteredcells = .offset(1, 6).resize(.rows.count - 1, 1).specialcells(xlcelltypevisible) debug.print rfilteredcells.address(0, 0) set rfilteredcells = nil 'turn .autofilter off .autofilter end

after filtering column f datetimes within 24 hours, i've shown quick methods of counting visible datetimes , setting range object visible (i.e. filtered) cells.

it wasn't clear on whether might have dates after now i've used strict 24 hr 'window'.

excel vba