Autofilter to stop if blank

[vmjan02]

I have the code below and is all good if the autofilter find's the data, but is there a way to shop the code if autofilter is blank. In this case if autofilter don't find "Ten"


Sheets("Sheet2").Select
Range("b4").Select
ActiveSheet.Range("$B$4:$AN$50000").AutoFilter Field:=35, Criteria1:="Ten"
Range("b4").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Eamil-10").Select
Range("a1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Call column
Call Line

 

[Fluff]

Re: Autofiler to stop if blank

How about

Dim usdRws As Long
With Sheets("Sheet2")
   If .AutoFilterMode Then .AutoFilterMode = False
   usdRws = Range("B" & Rows.count).End(xlUp).Row
   .Range("B4:AN" & usdRws).AutoFilter Field:=35, Criteria1:="Ten"
   If .Range("B" & Rows.count).End(xlUp).Row = 4 Then Exit Sub
   .Range("B4:AN" & usdRws).Copy Sheets("Eamil-10").Range("a1")
End With
Call Column
Call Line

 

[vmjan02]

Re: Autofiler to stop if blank

cool, this worked all well, but hw abt the next step

ActiveSheet.Range("$B$4:$AN$50000").AutoFilter Field:=36, Criteria1:="Hundred"
Range("b4").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Email-100").Select
Range("a1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Call column
Call Line

 

[Fluff]

Re: Autofiler to stop if blank

Simply rewrite that the same way as I did for "Ten"

 

[vmjan02]

Re: Autofiler to stop if blank

did the same, but once the Exit sub is done for "Ten", it don't goes for "Hundred"

Code
Dim usdRws As Long
With Sheets("Sheet2")
If .AutoFilterMode Then .AutoFilterMode = False
usdRws = Range("B" & Rows.Count).End(xlUp).Row
.Range("B4:AN" & usdRws).AutoFilter Field:=35, Criteria1:="Ten"
If .Range("B" & Rows.Count).End(xlUp).Row = 4 Then Exit Sub --- here the code ends.
.Range("B4:AN" & usdRws).Copy Sheets("Eamil-10").Range("a1")
End With
Sheets("Eamil-10").Select
Call column
Call Line

Sheets("Sheet2").Select
Range("b4").Select
ActiveSheet.Range("$B$4:$AN$50000").AutoFilter Field:=35
Selection.AutoFilter




Sheets("Sheet2").Select
Dim usdRw As Long
With Sheets("Sheet2")
If .AutoFilterMode Then .AutoFilterMode = False
usdRw = Range("B" & Rows.Count).End(xlUp).Row
.Range("B4:AN" & usdRw).AutoFilter Field:=36, Criteria1:="Hundred"
If .Range("B" & Rows.Count).End(xlUp).Row = 4 Then Exit Sub
.Range("B4:AN" & usdRw).Copy Sheets("Email-100").Range("a1")
End With
Sheets("Email-100").Select
Call column
Call Line

 

[Fluff]

Re: Autofiler to stop if blank

Is this what you want?

Dim UsdRws As Long
With Sheets("Sheet2")
   If .AutoFilterMode Then .AutoFilterMode = False
   UsdRws = Range("B" & Rows.count).End(xlUp).Row
   .Range("B4:AN" & UsdRws).AutoFilter Field:=35, Criteria1:="Ten"
   If .Range("B" & Rows.count).End(xlUp).Row > 4 Then
      .Range("B4:AN" & UsdRws).Copy Sheets("Eamil-10").Range("a1")
      .AutoFilterMode = False
      Call Column
      Call Line
   End If
   .Range("B4:AN" & UsdRws).AutoFilter Field:=36, Criteria1:="Hundred"
   If .Range("B" & Rows.count).End(xlUp).Row > 4 Then
      .Range("B4:AN" & UsdRws).Copy Sheets("Email-100").Range("a1")
      .AutoFilterMode = False
      Call Column
      Call Line
   End If
End With

 

[vmjan02]

Re: Autofiler to stop if blank

Hi, Thanks. did some modification and all is good now.

Dim UsdRws As Long
Sheets("sheet2").Select
With Sheets("Sheet2")
If .AutoFilterMode Then .AutoFilterMode = False
UsdRws = Range("B" & Rows.Count).End(xlUp).Row
.Range("B4:AN" & UsdRws).AutoFilter Field:=35, Criteria1:="Ten"
If .Range("B" & Rows.Count).End(xlUp).Row > 4 Then
.Range("B4:AN" & UsdRws).Copy Sheets("Eamil-10").Range("a1")
.AutoFilterMode = False
Sheets("Eamil-10").Select
Call column
Call Line
End If
Sheets("sheet2").Select
With Sheets("Sheet2")
If .AutoFilterMode Then .AutoFilterMode = False
UsdRws = Range("B" & Rows.Count).End(xlUp).Row
.Range("B4:AN" & UsdRws).AutoFilter Field:=36, Criteria1:="Hundred"
If .Range("B" & Rows.Count).End(xlUp).Row > 4 Then
.Range("B4:AN" & UsdRws).Copy Sheets("Email-100").Range("a1")
.AutoFilterMode = False
Sheets("Email-100").Select
Call column
Call Line
End If
End With
End With