Delete rows based on word in column across sheets

Hi again Hasson

In the attached revised file, I've modified your code as shown in bold below.

I added a new variable DelStr for flexibility (it can be changed for other scenarios), reset your row counter k and added a whole section near the end to clear the existing values (except header row 1),  add ONLY the "filtered" data and add borders to that range only.

Beware- when you run this, it will overwrite your original data (with the "BALANCE" rows). You can choose to save or not save that result (and re-run the code) of course.

Sub deleterows_v3()
  Dim sh As Worksheet
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  Dim DelStr As String

  ' change this as required, e.g. to TOTAL
  DelStr = "BALANCE"

  For Each sh In Sheets
    a = sh.Range("A2:H" & sh.Range("H" & Rows.Count).End(3).Row).Value
    ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
    ' reset counter
    k = 0

    For i = 1 To UBound(a, 1)
        ' don't copy lines with DelStr value in column A to array b
        If a(i, 1) <> DelStr Then
          k = k + 1
          For j = 1 To UBound(a, 2)

            b(k, j) = a(i, j)
          Next j
        End If
    Next i

    'clear the target range (after headers)
    With sh.Range("A1")
        .CurrentRegion.Offset(1, 0).Clear
        ' add results but ignoring unfilled rows
        With .Offset(1, 0).Resize(k, UBound(b, 2))
            .Value = b
            ' add borders
            With .Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
            End With
        End With
    End With
  Next sh
  ' tell user what happened
  MsgBox "All rows containing " & DelStr & " removed (case-sensitive)"

End Sub

Hope this fixes your problem. If so, please mark this Answer as Selected.