Filter Code

[Ray Kanner]

I have a large table with employee ids in asscending order
in the third column. An employee id record is not unique,
so there can be many employee records per employee. Using
a filter, I would like to view all the records pertaining
to one employee at a time. Does anyone have sample code
they could share that would allow the filter to advance to
the next employee id number when a (spin) button assigned
to it is pressed. Thanks loads.

Ray Kanner (e-mail address removed)

 

[Cecilkumara Fernando]

Ray,
It can be done, but with the data you supplied, it is near impossible to
write a code.
So come back with a small sample of your table and how the filtered data
should look like.
I am sure that you will get a good response.
Cecil

 

[Bernie Deitrick]

Ray,

The two macros below will set up your sheet to do this. Assumptions: G1 is
currently empty, and your data table starts in A1, and your table is the
only data on the worksheet, and your data is contiguous (no blank rows or
columns). The first macro will set up the spinner, and the second is called
by the spinner to do the filtering of your data set. Once the spinner is set
up, you can drag it to some place convenient.

HTH,
Bernie
MS Excel MVP

Sub SetUpSpinner()
Dim myVRange As Range
Dim myCell As Range

Range("C1", Range("C1").End(xlDown)).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("C65536").End(xlUp)(3), _
Unique:=True
Set myVRange = Range("C65536").End(xlUp).CurrentRegion

ActiveSheet.Spinners.Add(100, 100, 40, 70).Select
With Selection
.Value = 0
.Min = 2
.Max = myVRange.Cells.Count
.SmallChange = 1
.LinkedCell = "$G$1"
.OnAction = "FilterRange"
.Placement = xlFreeFloating
.Visible = True
.Enabled = True
End With

End Sub
Sub FilterRange()
Dim myFRange As Range
Dim myVRange As Range

Set myFRange = Range("C1").CurrentRegion
Set myVRange = Range("C65536").End(xlUp).CurrentRegion

myFRange.AutoFilter Field:=3, Criteria1:=myVRange(Range("G1").Value)
End Sub

 

[Ron de Bruin]

Hi Ray

Maybe you like this example that copy the records of
every employee in it's own sheet
http://www.rondebruin.nl/copy5.htm

See the last macro

 

[Paul Robinson]

Hi Ray,
Doesn't that imply you are filtering on employee rather than employee
ID? Why not just do that from the employee filter dropdown??
regards
Paul