Autofilter when no rows meet criteria (VBA)

alyssa75

Board Regular
Joined
May 14, 2007
Messages
240
I have the code which filters my spreadsheet and copies that dataset but I think I need to add an IF that skips the copy line when the criteria is not met. Any ideas?

Thanks all!!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,052
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
 

alyssa75

Board Regular
Joined
May 14, 2007
Messages
240
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,052
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Try not filtering the entire row:
Code:
Range("A2").AutoFilter Field:=34, Criteria1:=myKey

Regards,
Rory
 

alyssa75

Board Regular
Joined
May 14, 2007
Messages
240
Thanks, but this generates a debugging error:

"Autofilter method or Range class failed"

Any other ideas??

Thanks!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,052
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
 

Myszak

New Member
Joined
Oct 8, 2015
Messages
1
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!
 

Forum statistics

Threads
1,171,305
Messages
5,874,927
Members
433,082
Latest member
wanders

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top