Locate matched criterea & delete Rows!

[Zshanar69]

Hi,
I have master sheet called "ENTITY"
In column "A" if there's a text "NEXT" but in the same Row column "B" is empty.
now there may be 3 or 300 or 3000 Rows with same criteria,
I want last entry to be found with same criteria & right below that row column "D" ,"G" & "K" must be cleared without changing other cells in single Row,
& below that
every Row to last filled row of column "A" would be deleted!
if anyone could please suggest any VBA macro!

Thanks.

 

[Zshanar69]

It shows : Run-time error '1004':
Application-defined or object-defined error!

In auto filter 1 line it indicates yellow!

 

[Fluff]

Did you get that error on the previous code?

 

[Zshanar69]

Did you get that error on the previous code?

Nop! But it was deleting last one single Row.

 

[Fluff]

In that case has anything changed in your data, as that line of code has not changed.

 

[Zshanar69]

Ok, how about

Sub Zshanar()
   Dim Rng  As Range
   Dim UsdRws As Long
  
   With ActiveSheet
      UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
      .Range("A1:B" & UsdRws).AutoFilter 1, "next"
      .Range("A1:B" & UsdRws).AutoFilter 2, ""
      Set Rng = Range("A" & Rows.Count).End(xlUp)
      .AutoFilterMode = False
   End With
   Intersect(Rng.Offset(1).EntireRow, Range("D:D,G:G,K:K")).ClearContents
   Range(Rng.Offset(2), Range("A" & UsdRws)).EntireRow.Delete
End Sub

Nothing changed in data, your previous code had autofilter lines .Range(A:B now its A1:B I replaced both & code works just fine!
Thanks,

 

[Fluff]

Glad you sorted it & thanks for the feedback.