Dynamic Filter

[Shrinivas]

I have list in following fashion.

A B C D
Act Serial number Month Units
100412403 8604158 Sep-17 351
100412403 8604158 Aug-17 396
100412403 8604158 Jul-17 379
100412403 8604158 Jun-17 407

I have 25000 rows (or more)(records), I want to filter by column 'A' & compare each record in column 'D'. Can we develop something like arrow button so by hitting it next record in column 'A' will get filtered, instead of selecting number every time ?
Thanks in advance,
shrinivas.

 

[NARAYANK991]

Hi ,

See if this helps ; the code is as follows :

Public nextvalue As Boolean


Sub Button1_Click()
    nextvalue = True
End Sub

Public Sub FilterOneByOne()

'          In the References under Tools , select Microsoft Scripting Runtime
'          since the usage of a dictionary is dependent on this
           Dim lastrow As Long, i As Long, counter As Long
           Dim inputdatarange As Range, cell As Range
           Dim cellval As Variant
           Dim dict As New Dictionary
           
           lastrow = Me.Range("A" & Rows.Count).End(xlUp).Row
           Set inputdatarange = Me.Range("A2:A" & lastrow)
           
           If Me.AutoFilterMode Then inputdatarange.AutoFilter
           
           For Each cell In inputdatarange
               If Not (dict.Exists(cell.Value)) Then
                  dict.Add cell.Value, cell.Value
               End If
           Next
                  
           For Each cellval In dict.Keys
               nextvalue = False
               counter = 0
               inputdatarange.Offset(-1).AutoFilter field:=1, Criteria1:=cellval
               Do While Not nextvalue
                  counter = counter + 1
                  DoEvents
                  If counter > 100000# Then nextvalue = True
               Loop
           Next
           
           inputdatarange.AutoFilter
End Sub

There is a time delay between each selection , so that even if you do not press the NEXT button , when the time delay expires , it will automatically move on to the next ID.

The workbook itself can be downloaded from here :

https://www.dropbox.com/s/3l5xwxko3mf2kqg/Sample File.xlsm?dl=0

 

[NARAYANK991]

Hi ,

The revised file is here : https://www.dropbox.com/s/3l5xwxko3mf2kqg/Sample File.xlsm?dl=0

 

[Shrinivas]

Thanks a Ton. It works.
Thanks again for quick reply.
shrinivas.

 

[Shrinivas]

Works like magic
Thanks
shrinivas

 

[Shrinivas]

sorry but this files enables other files but it shows only one value of from month & units head & timer is very fast