Filter macro stops if result is blank

aravindhan_31

Well-known Member
Joined
Apr 11, 2006
Messages
672
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
I have recorded the below macro for filtering the values in column 35 with Value contains "Canc" and then filter col B & C with blanks then fill in all the blank cells Col B & C as "To be Cancelled" and "Invalid" respectively
once this is done again the it filters in Column 16 with Credit and then fill in all the blank cells Col B & C as "Credit" and "Credit" respectively.
Code:
sub fill_blanks
Sheets("Unique").Select
    LR = Range("A" & Rows.Count).End(xlUp).Row
'Field 35
   ActiveSheet.Range("A1:AS" & LR).AutoFilter Field:=35, Criteria1:= _
        "=*Canc*", Operator:=xlAnd
    ActiveSheet.Range("$A$1:AS" & LR).AutoFilter Field:=2, Criteria1:="="
    ActiveSheet.Range("$A$1:AS" & LR).AutoFilter Field:=3, Criteria1:="="
    Range("$B$1:B" & LR).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
      Selection.FormulaR1C1 = "To be Cancelled"
    Range("$C$1:C" & LR).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "Invalid"
    Selection.AutoFilter
    Range("B1").Select
' Field 16
ActiveSheet.Range("A1:AS" & LR).AutoFilter Field:=16, Criteria1:= _
        "=*Credit*", Operator:=xlAnd
    ActiveSheet.Range("$A$1:AS" & LR).AutoFilter Field:=2, Criteria1:="="
    ActiveSheet.Range("$A$1:AS" & LR).AutoFilter Field:=3, Criteria1:="="
    Range("$B$1:B" & LR).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "Credit"
    Range("$C$1:C" & LR).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "Credit"
    Selection.AutoFilter
    Range("B1").Select
the code works fine if there are results for the criteria "Canc" & Credit
but if no results exists for any one then I get an error message on the line
Code:
 Selection.SpecialCells(xlCellTypeBlanks).Select
and the macro stops
the reason fo this is when there is no results there will not be any blank cells, so macro cant select.
how do I change this to work if no result just skip it and proceed with the next one.

Thanks for your help
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
i'm sure there are better ways, though i'm probably guilty of using on error resume next
 
Upvote 0
you are right using on error gives loads of trouble

any alternate way...
 
Upvote 0

Forum statistics

Threads
1,224,518
Messages
6,179,248
Members
452,900
Latest member
LisaGo

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
Back
Top