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.

 

[Fluff]

Do you want to find the last row where col A is NEXT or the last row where col A is NEXT and col B is empty?

 

[Zshanar69]

Where col A is NEXT & col B is empty!

Do you want to find the last row where col A is NEXT or the last row where col A is NEXT and col B is empty?

 

[Fluff]

Ok, how about

Sub Zshanar()
   Dim Rng  As Range
   
   With ActiveSheet
      .Range("A1:B1").AutoFilter 1, "next"
      .Range("A1:B1").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" & Rows.Count).End(xlUp)).EntireRow.Delete
End Sub

 

[Zshanar69]

It's just deleting the last Row!

 

[Zshanar69]

Did I miss something in macro?

 

[Fluff]

You have blank rows within the data that is causing the problem.
I've got to pop out for a few minutes, but will sort it out when I get back.

 

[Zshanar69]

Thanks!

 

[Fluff]

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

 

[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

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