AutoFilter to Named Range

  • Thread starter Thread starter odie
  • Start date Start date
O

odie

I have a sheet of data I want to programatically filter (by track) into
subsets and assign the filtered data to named ranges.
Sample data:

Track PosX PosY
4 33 41
2 19 73
2 21 68
4 36 38
6 83 12
4 35 38
4 36 40
6 81 14
2 23 67

So, for example, a named range "track4" would consist of all the PosX and
PosY data corresponding to track4.
Can anyone suggest some code to start with?
 
How about this:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim myUniqueCells As Range
Dim myRng As Range
Dim myCell As Range
Dim trkRng As Range

Set wks = Worksheets("sheet1")

With wks
Set myRng = Intersect(.UsedRange, .Columns(1))
End With

With myRng
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set myUniqueCells = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.Cells.SpecialCells(xlCellTypeVisible)

For Each myCell In myUniqueCells.Cells
.AutoFilter field:=1, Criteria1:=myCell.Value
Set trkRng = .Offset(1, 0).Resize(.Rows.Count - 1, 3) _
.Cells.SpecialCells(xlCellTypeVisible)
trkRng.Name = "Track" & myCell.Value
Next myCell
End With

wks.AutoFilterMode = False
End Sub

The sub above includes Column A, too.

If you don't want that, replace this line:
Set trkRng = .Offset(1, 0).Resize(.Rows.Count - 1, 3) _
with:
Set trkRng = .Offset(1, 1).Resize(.Rows.Count - 1, 2) _
 
Back
Top