When filtering data the website has focused acutely on filtering information which meets a certain criteria or between two criteria. The site has dealt with filtering on multiple criteria and filtering between dates. Some of these concepts are quite difficult as repeating Excel's tick by filter is not always easy to replicate in VBA. I came across an interesting problem while working for a client. The range of cells had the date and time in the cell, the idea was to get quantity data from a dataset if it was past a certain time.
The cell data contains a bit of information if you look closely. If you inspect a filter which has date and time you will see that there is (year, month, day, time). Ah ha - not very easy to trap this sort of information with code so I will work on the concept that we need a helper column for this purpose. We can use the helper column to trap the data which is past say 6 pm. Using the 24 hour clock this time is 18:00 or 18 in filter speak. So Let's try and isolate the information with Excel VBA. Firstly, we will declare our variables. Next we will use the last column plus 1 to put some helper formula in. This will be deleted after the procedure runs. The formula will test if Column D contains sales after 6pm and the formula is as follows.
In VBA we put the formula in quotation marks "=IF(HOUR(D2)>=18,1,0)" to make sure it is read into the cells as text.
The 18 I have highlighted in RED above is the time. Anything greater than 6pm will see a 1 placed in the column. After the formula has been dynamically placed from row 2 to the last used row in the column adjacent a filter is applied on the number 1. The results are then copied to our output sheet (Sheet2) and the filter is turned off and the formula is removed.
Option Explicit
Sub FilterHelperCol()
Dim lr As Long
Dim rng As Range
Dim sh As Worksheet
Set sh = Sheet1 'Sheet with the data
lr = sh.Range("A" & Rows.Count).End(xlUp).Row
Set rng = sh.Range("A1:L" & lr) 'Key Range which holds the Data
rng.Offset(1, rng.Columns.Count).Resize(lr - 1, 1) = "=IF(HOUR(D2)>=18,1,0)" 'Helper Formula
rng.Offset(, rng.Columns.Count).Resize(lr - 1, 1).AutoFilter 1, 1
rng.Copy Sheet2.[A10]
rng.AutoFilter
rng.Offset(1, rng.Columns.Count).Resize(lr - 1, 1).ClearContents
End Sub
The following Excel file outlines the concept. The column with the time in it is in Column D. All data is returned to the output page which meets the criteria (greater than 6pm).
