Autofilter

[Jasminder Dhaliwal]

Hello experts,

The following code is coming up with the error - This
command requires at least two rows of data.

The list that it is filtering has 6 rows, does anyone know
why this is happening?

Thanks so much

Sub ExtractUniqueValues()
Dim mySheet As Worksheet
Set mySheet = Worksheets("Crystal_Table")
Range("H1:H" & Range("H65536").End(xlUp).Row) _
.AdvancedFilter Action:=xlFilterInPlace,
Unique:=True

Range("H1:N" & Range("H65536").End(xlUp).Row) _
.SpecialCells(xlCellTypeVisible).Copy
Sheets("Email_Control").Select
Range("A1").Select
ActiveSheet.Paste
Range("B1,D1:F1").EntireColumn.Delete
Range("A1").EntireRow.Delete
mySheet.ShowAllData
End Sub

 

[Dave Peterson]

My first guess is it's because of your unqualified range references.

If "crystal_table" isn't the activesheet, you could have trouble:

Option Explicit
Sub ExtractUniqueValues()
Dim mySheet As Worksheet
Set mySheet = Worksheets("Crystal_Table")
With mySheet
.Range("H1:H" & .Range("H65536").End(xlUp).Row) _
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True

.Range("H1:N" & .Range("H65536").End(xlUp).Row) _
.SpecialCells(xlCellTypeVisible).Copy
End With

With Sheets("Email_Control")
.Select
.Range("A1").Select
.Paste
.Range("B1,D1:F1").EntireColumn.Delete
.Range("A1").EntireRow.Delete
End With
mySheet.ShowAllData
End Sub