Autofilter when no rows meet criteria (VBA)

RoryA

MrExcel MVP, Moderator

Joined

May 2, 2008

Messages

42,259

Office Version

1. 365
2. 2016

Platform

1. Windows
2. MacOS

• May 28, 2008

• #2

Code:

activesheet.autofilter.range.columns(1).specialcells(xlcelltypevisible).count

should return 1 if only the header cell is visible - i.e. nothing matches the filter.
HTH
Rory

 

Upvote 0

A

alyssa75

Board Regular

Joined

May 14, 2007

Messages

240

• May 28, 2008

• #3

Thanks - for some reason - if the criteria is not met, the filter does nothing (the entire dataset remains visible).

Here is the code:

With Workbooks(myFile).Sheets("Sheet1")
.Activate
.AutoFilterMode = False
End With
Rows("2:2").AutoFilter Field:=34, Criteria1:=myKey
If ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
With Range(HCFile_StartColumn).CurrentRegion
.Resize(.Rows.Count - 2, 14).Offset (2, 2).SpecialCells(xlCellTypeVisible).Copy
End With

Blah Blah Blah

End If

 

Upvote 0

RoryA

MrExcel MVP, Moderator

Joined

May 2, 2008

Messages

42,259

Office Version

1. 365
2. 2016

Platform

1. Windows
2. MacOS

• May 28, 2008

• #4

Try not filtering the entire row:

Code:

Range("A2").AutoFilter Field:=34, Criteria1:=myKey

Regards,
Rory

 

Upvote 0

A

alyssa75

Board Regular

Joined

May 14, 2007

Messages

240

• May 28, 2008

• #5

Thanks, but this generates a debugging error:

"Autofilter method or Range class failed"

Any other ideas??

Thanks!

 

Upvote 0

A

alyssa75

Board Regular

Joined

May 14, 2007

Messages

240

• May 29, 2008

• #6

Bump

 

Upvote 0

RoryA

MrExcel MVP, Moderator

Joined

May 2, 2008

Messages

42,259

Office Version

1. 365
2. 2016

Platform

1. Windows
2. MacOS

• May 29, 2008

• #7

Sorry - lost this one!
Does this work:

Code:

With Workbooks(myFile).Sheets("Sheet1")
   .Activate
   .AutoFilterMode = False
   .Range("AH2").AutoFilter
   .Range("AH2").AutoFilter Field:=1, Criteria1:=myKey
   If .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
      With .Range(HCFile_StartColumn).CurrentRegion
         .Resize(.Rows.Count - 2, 14).Offset(2, 2).SpecialCells(xlCellTypeVisible).Copy
      End With
   End If
End With

Regards,
Rory

 

Upvote 0

A

alyssa75

Board Regular

Joined

May 14, 2007

Messages

240

• May 29, 2008

• #8

A slight variation of it did!! Thanks so much for your help!

 

Upvote 0

M

Myszak

New Member

Joined

Oct 8, 2015

Messages

1

• Oct 8, 2015

• #9

May I ask you for the "slight variation" that finally made it work !?
(as I am probably stuck at the same point on the way to a solution)

THX in advance!

 

Upvote 0