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

 

[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

 

[kevin9999]

So the only 2 sheets you're interested in are called "sh" and "mn"? If that's correct, try the following on a copy of your workbook.

Option Explicit
Sub Mussala()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "sh", "mn"
            With ws.Range("A1").CurrentRegion
                .AutoFilter 1, "TOTAL"
                If ws.Cells(Rows.Count, 1).End(xlUp).Row > 1 Then
                    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
                End If
                .AutoFilter
            End With
        End Select
    Next ws
End Sub