Autofilter with headers in Row 2

Gtabtr1

New Member
Joined
Feb 15, 2017
Messages
39
I'm sure this is a simple answer but I'm fairly frustrated trying to get it to work and not being able to identify why it is not working. My data headers are in row 2. Most of Row 1 is empty, out of 16 used columns, only 2 have data. But I need my autofilter to use row 2 are the header row. I'm getting the dreaded "Autofilter method of range class failed". Please! Please! What am I missing?

Dim AreaList As Range
Dim area As Range
Set AreaList = Workbooks(Pretty).Sheets(List).Range("A2:A" & last)

Workbooks(Master).Sheets(Participants).Activate
Workbooks(Master).Sheets(Participants).Select
Dim RangetoFilter As Variant
Dim Headerrow As Variant
RangetoFilter = "A3:P" & Lastrow
Headerrow = "A2:P2"
For Each area In AreaList

ActiveSheet.AutoFilterMode = False
ActiveSheet.Range(Headerrow).Select
ActiveSheet.Range(Headerrow).AutoFilter
ActiveSheet.Range(RangetoFilter).AutoFilter Field:=5, Criteria1:=area, Operator:=xlFilterValues
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
Do you have any blank rows within your data?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
Ok, one other thing, are your trying to filter for each individual value in the area list, or all of them?
 

Gtabtr1

New Member
Joined
Feb 15, 2017
Messages
39

ADVERTISEMENT

I'm trying to filter on one number at a time from a list where the number could be included once or 20 times for example. So one value at a time.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
   Dim AreaList As Range, Cl As Range

   Set AreaList = Workbooks(Pretty).Sheets(List).Range("A2:A" & last)

   With Workbooks(Master).Sheets(Participants)
      For Each Cl In AreaList
         .Range("A2:P2").AutoFilter 5, Cl.Value
         ' do something
      Next Cl
   End With
 

Gtabtr1

New Member
Joined
Feb 15, 2017
Messages
39

ADVERTISEMENT

I changed "C1" to "Area" to help me understand what it is but other than that I still get the "Autofilter method of range class failed" error on the ".Range("A2:P2").AutoFilter 5, Area.Value" line.

Cell A1 is empty. Headers start in A2. Does that matter?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
Is your data on a protected sheet and/or is it a structured table?
 

Gtabtr1

New Member
Joined
Feb 15, 2017
Messages
39
No. It's just an Excel spreadsheet with data in A1:C42 but the area I need (numeric values) are in column A. A2 is the first value. A42 is the last numeric value.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
I was referring to the sheet that you are trying to filter, rather than the arealist.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,364
Members
412,321
Latest member
Yusuf_A
Top