Deleting few columns based on a criteria

Dear All,

Good Evening!!!

My below requirement might confuse you, so request you to please go through the attached excel Where I have done changes for the first brand name in the before sheet & after sheet

Its just about deleting content/rows when my requirement meets criteria.

I prepared a format in such a way that one brand has several models for calculating their order, opening stock, Quantity received etc

I want to delete entire row for every brand where Remarks has Quantity received as per order & Ordered Quantity, Opening Stock, Quantity Received & Quantity to be received = zero

I want rows to remain after deleting = max number of rows it occupied under the item shown below

ex: for KEDBROOKE Brand, I've max number of items are 2, So I want to delete next entire rows with remaining 2 rows without deleting

I just also want to delete content (not rows) in the rows that are remain after deleting where where Remarks has Quantity received as per order & Ordered Quantity, Opening Stock, Quantity Received & Quantity to be received = zero

ex: for KEDBROOKE Brand, I've max number of items are 2, So if I have Remarks has Quantity received as per order & Ordered Quantity, Opening Stock, Quantity Received & Quantity to be received = zero with in these 2 rows

DOWNROD/SHACKLE:
CANOPY
ROTOR:
SHAFT:
CAPACITOR:/MAKE
STATOR:
Lamination
BLADE:
MOTOR BOX:
THERMOCOLE TRAY:
STAY CAP / CENTRE PAD:
BEARING
SHOW CAP/ RING
MASTER CARTON:

I hope I didnt confuse you & Request you to please help.

Hope this helps!

the Macro "Delete_Erase_Rows" should perform the desired action on the "before" worksheet.

The macro code is as follows:

Sub Delete_Erase_Rows()
Dim j As Integer, iLastRow As Integer, sText As String
With ThisWorkbook.Worksheets("before")                 'replace before with the name of the actual worksheet
     iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
     sText = "Quantity received as per order"
     For j = iLastRow To 2 Step -1
          If .Range("B" & j) = "" And .Range("H" & j) = "" And .Range("N" & j) = "" And .Range("T" & j) = "" _
          And .Range("T" & j) = "" And .Range("AF" & j) = "" And .Range("AL" & j) = "" And .Range("AR" & j) = "" _
          And .Range("AX" & j) = "" And .Range("BD" & j) = "" And .Range("BJ" & j) = "" And .Range("BP" & j) = "" _
          And .Range("BV" & j) = "" And .Range("CV" & j) = "" Then .Range("B" & j).EntireRow.Delete
          Next j
     
     iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
     For j = iLastRow To 2 Step -1
          If .Range("C" & j) = 0 And .Range("D" & j) = 0 And .Range("E" & j) = 0 And .Range("F" & j) = 0 And _
          .Range("G" & j) = sText Then .Range("B" & j & ":G" & j).ClearContents
          If .Range("I" & j) = 0 And .Range("J" & j) = 0 And .Range("K" & j) = 0 And .Range("L" & j) = 0 And _
          .Range("M" & j) = sText Then .Range("H" & j & ":M" & j).ClearContents
          If .Range("O" & j) = 0 And .Range("P" & j) = 0 And .Range("Q" & j) = 0 And .Range("R" & j) = 0 And _
          .Range("S" & j) = sText Then .Range("N" & j & ":S" & j).ClearContents
          If .Range("U" & j) = 0 And .Range("V" & j) = 0 And .Range("W" & j) = 0 And .Range("X" & j) = 0 And _
          .Range("Y" & j) = sText Then .Range("T" & j & ":Y" & j).ClearContents
          If .Range("AA" & j) = 0 And .Range("AB" & j) = 0 And .Range("AC" & j) = 0 And .Range("AD" & j) = 0 And _
          .Range("AE" & j) = sText Then .Range("Z" & j & ":AE" & j).ClearContents
          If .Range("AG" & j) = 0 And .Range("AH" & j) = 0 And .Range("AI" & j) = 0 And .Range("AJ" & j) = 0 And _
          .Range("AK" & j) = sText Then .Range("AF" & j & ":AK" & j).ClearContents
          If .Range("AM" & j) = 0 And .Range("AN" & j) = 0 And .Range("AO" & j) = 0 And .Range("AP" & j) = 0 And _
          .Range("AQ" & j) = sText Then .Range("AL" & j & ":AQ" & j).ClearContents
          If .Range("AS" & j) = 0 And .Range("AT" & j) = 0 And .Range("AU" & j) = 0 And .Range("AV" & j) = 0 And _
          .Range("AW" & j) = sText Then .Range("AR" & j & ":AW" & j).ClearContents
          If .Range("AY" & j) = 0 And .Range("AZ" & j) = 0 And .Range("BA" & j) = 0 And .Range("BB" & j) = 0 And _
          .Range("BC" & j) = sText Then .Range("AX" & j & ":BC" & j).ClearContents
          If .Range("BE" & j) = 0 And .Range("BF" & j) = 0 And .Range("BG" & j) = 0 And .Range("BH" & j) = 0 And _
          .Range("BI" & j) = sText Then .Range("BD" & j & ":BI" & j).ClearContents
          If .Range("BK" & j) = 0 And .Range("BL" & j) = 0 And .Range("BM" & j) = 0 And .Range("BN" & j) = 0 And _
          .Range("BO" & j) = sText Then .Range("BJ" & j & ":BO" & j).ClearContents
          If .Range("BQ" & j) = 0 And .Range("BR" & j) = 0 And .Range("BS" & j) = 0 And .Range("BT" & j) = 0 And _
          .Range("BU" & j) = sText Then .Range("BP" & j & ":BU" & j).ClearContents
          If .Range("BW" & j) = 0 And .Range("BX" & j) = 0 And .Range("BY" & j) = 0 And .Range("BZ" & j) = 0 And _
          .Range("CA" & j) = sText Then .Range("BV" & j & ":CA" & j).ClearContents
          If .Range("CC" & j) = 0 And .Range("CD" & j) = 0 And .Range("CE" & j) = 0 And .Range("CF" & j) = 0 And _
          .Range("CG" & j) = sText Then .Range("CB" & j & ":CG" & j).ClearContents
          Next j
     End With
End Sub