Column filter by VBA (button)

[2avrham]

I have dates column in my pivot table data sheet that I want to filter according to my needs.
Date column looks like this:<o></o>
complete_date
2018-05-0917:27:20.373
2018-05-0917:28:57.177<o></o>
2018-05-0918:57:52.680<o></o>
2018-05-0919:50:53.553<o></o>
2018-05-0920:20:49.013
2018-05-0920:37:03.977
2018-05-0920:42:55.470<o></o>
2018-05-0921:08:40.143
2018-05-1000:01:57.877<o></o>
2018-05-1000:02:14.637
2018-05-1000:02:30.010
2018-05-1001:08:54.687<o></o>
2018-05-1001:12:53.250
2018-05-1001:30:43.003<o></o>
2018-05-1002:12:58.333<o></o>
2018-05-1002:18:55.507<o></o>
2018-05-1002:39:15.937
2018-05-1003:17:06.147<o></o>
2018-05-1003:26:13.440<o></o>
2018-05-1003:41:55.437

I want to have the ability in
Excel sheet to click on button and there I will able to filter according the dates.
One option is to filter from current time till the time in hours that I entered, for example if I will enter "5" I will get all dates that completed in the last 5 hours.

2. I what to have the ability to filter according time frame for example filter will bring all data from "4"
hours ago till "14". How can I do such thing? thanks!!<o></o>

 

[ranman256]

Re: Column filter by VBA (buttomn)

you can create a form and enter the start time-end time range.
I dont know what "14" means. 5hrs to 14 hrs? (use a form for the range in the code, vStart, vEnd)

Sub Macro1()
Dim y, m, d, h, n, s
Dim vStart As Date, vEnd As Date, vDte
Dim iNum As Integer

iNum = InputBox("# Hrs ago", "Enter Hrs")

Range("B1").Value = "TimeFormat"
Range("C1").Value = "Found"
Range("A2").Select
While ActiveCell.Value <> ""
    y = Left(ActiveCell.Value, 4)
    m = Mid(ActiveCell.Value, 6, 2)
    d = Mid(ActiveCell.Value, 9, 2)
    h = Mid(ActiveCell.Value, 11, 2)
    n = Mid(ActiveCell.Value, 14, 2)
    s = Mid(ActiveCell.Value, 17, 2)
    
   vDte = m & "/" & d & "/" & y & " " & h & ":" & n & ":" & s
   ActiveCell.Offset(0, 1).Value = vDte
   
   ActiveCell.Offset(1, 0).Select  'next row
   vEnd = vDte
Wend

vStart = DateAdd("h", -iNum, vEnd)   'get # hrs ago
ActiveCell.Offset(-1, 1).Select   'prev row
While ActiveCell.Row > 1
   If ActiveCell.Value >= vStart Then ActiveCell.Offset(0, 1).Value = "true"
   
   ActiveCell.Offset(-1, 0).Select 'prev row
Wend

End Sub

 

[2avrham]

Re: Column filter by VBA (buttomn)

Hi,

I cannot run it on pivot table data (at form option)... any way when I copy my data to different sheet im getting error on "Value = vDte" any idea why?

in addition my table contains many columns and dates data found on "O" column, if I do not want to print anything I can remove the "Range("B1").Value = "TimeFormat"
Range("C1").Value = "Found""
?

thanks!

 

[2avrham]

Re: Column filter by VBA (buttomn)

I think I overcome the issue but now im getting error: at "ActiveCell.Offset(-1, 1).Select 'prev row"

any idea why?