Conditional statement for Excel VBA code

Question

I need a little help. Let's start with the code:

Range("a1").AutoFilter Field:=1, Criteria1:="W1793-PAS (Agency)"
'exclude 1st row (titles)
With Intersect(Range("a1").CurrentRegion, _
               Range("2:60000")).SpecialCells(xlCellTypeVisible)
    .Rows.Delete
End With
ActiveSheet.ShowAllData
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

I need to create a conditional statement for the above code that will stop the procedure if there is no text matching Criteria1 (W1793-PAS (Agency)). I don't want this to run if there is no rows with W1793-PAS (Agency) in it.

Siddharth Rout · Accepted Answer · 2017-06-06 16:52:08Z

1

Simply check if the Autofilter is returning anything or not by storing the filtered range in a Range object and then delete it

Dim rng As Range

'Remove any filters
ActiveSheet.AutoFilterMode = False

With Range("A1")
  .AutoFilter Field:=1, Criteria1:="W1793-PAS (Agency)"
  '~~> Set this here
  Set rng = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
End With

'Remove any filters
ActiveSheet.AutoFilterMode = False

'~~> Check if there were any filtered results and then delete
If Not rng Is Nothing Then rng.Delete

answered Jun 6, 2017 at 16:52

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Conditional statement for Excel VBA code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related