delete rows contains TOTAL word across sheets

[Mussala]

Hi
I have rows some of them contains TOTAL word in column A for just two sheets(sh, mn) . I want deleting the whole rows contain TOTAL word in column A for two sheets.
thanks

 

[kevin9999]

Try the following - tested on 2 sheets of 25K rows around 0.25 seconds.

Option Explicit
Sub Mussala_V2()
    Application.Calculation = xlManual
    Application.ScreenUpdating = False
    Dim ws As Worksheet, LRow As Long, LCol As Long, i As Long, a, b
    
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "sh", "mn"
            LRow = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
            LCol = ws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
            a = Range(ws.Cells(2, 1), ws.Cells(LRow, 1))
            ReDim b(1 To UBound(a), 1 To 1)
            For i = 1 To UBound(a)
                If a(i, 1) = "TOTAL" Then b(i, 1) = 1
            Next i
            ws.Cells(2, LCol).Resize(UBound(a)) = b
            i = WorksheetFunction.Sum(ws.Columns(LCol))
            If i > 0 Then
                ws.Range(ws.Cells(2, 1), ws.Cells(LRow, LCol)).Sort Key1:=ws.Cells(2, LCol), _
                order1:=xlAscending, Header:=xlNo
                ws.Cells(2, LCol).Resize(i).EntireRow.Delete
            End If
        End Select
    Next ws
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
End Sub

 

[Anthony47]

Filter column A for the condition "Contains ... Total"; then select the displayed rows and Delete them.

 

[Mussala]

I search for macro , not manually .

 

[Anthony47]

You might record a macro:
-start from Sheet2
-start recording the macro
-select sheet1, apply filter to column A, filter for "Contains ... Total"
-select fromthe visible rows and delete them
-remove the filter
-move to sheet2 and repeat
-stop recording when job done
Examine the recorded code; it migh be wise to extend the rows selected (ready to be deleted) up to (say) line 10,000 (or a value surely above your lines)

Try...

 

[Mussala]

ok i will try it, and comeback when I need help , despite of I don't use before .

 

[Anthony47]

ok i will try it, and comeback when I need help , despite of I don't use before

Recording a macro is a great feature, and returns code properly working in most of the cases.
If you post the code that you get we can review it and maybe suggest some improvements

 

[Mussala]

when I do this step
-select sheet1, apply filter to column A, filter for "Contains ... Total"
then will hide the others rows . so how do I this is step
-select fromthe visible rows and delete them
if the others rows are hidden ?!

 

[Anthony47]

After the filter, only rows with Total are visible; if you select all the visible rows (except the header) and delete them then you you should get what you look far: "delete rows contains TOTAL word" (hidden rows cannot be selected and thus will not be deleted)

 

[Mussala]

here is what I got

Sub Macro1()
'
' Macro1 
'

'
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$1969").AutoFilter Field:=1, Criteria1:= _
        "TOTAL"
    Rows("3:52").Select
    Selection.Delete Shift:=xlUp
End Sub