Removing Data From Cells if a certian condition exists

[Bobbbo]

This one is kind of strange, but I am certain one of you have a solution.

I need to change a spreadsheet that has data entered into the wrong place. The attach list of fruit of Apples, Pears and Peaches have the type of that fruit below. Then totals that fruit. There should not be values in the rows next to 1000 Apples, 2000 Pears and 3000 Peaches. I still need the total but need the data removed from (or set to zero) and row that has the prefix of "Total". So in particular, there is a cell in column E that has the prefix of "Total". The first occurrence is E6, "Total 1000 Apples". I need the VBA code to look through the column E and find any occurrence of "Total 1000 Apples" but without the Word "Total", just "1000 Apples", then zero out the data in the columns to the right. Then repeat this for "Total 2000 Pears" zero out data in row "2000 Pears"; and "Total 3000 Peaches" zero out data in row "3000 Peaches".

The Spreadsheet has a desired result below the top Spreadsheet.

The spreadsheet always starts in cell E2, the names are only in Column E, the number of rows can be of any length, the name must be an exact match (case sensitive), the name may or may not have numbers, and the data will be in columns F through S to the right of the found name.

Thanks in advance.

[AB33]

If the layout remains the same, filter is the best code.

[Bobbbo]

Not quit sure what you mean. Would that be a formula?

[AB33]

Remove the output data from the sheet and run the code. I have already run the code and see the result.

[Bobbbo]

AB33, I looked at the code again and it will not do what I am asking for as your code has the names ("1000 Apples", "2000 Pears", "3000 Peaches") which I will not know. These names could be anything, I just need it to find the same name for any account that starts with "Total".

[dmcgov]

from the excel main menu, choose DATA. Now select some columns that you would like to filter. Then from the DATA menu, select Filter (in the middle). Then pick one of the arrows next to column and filter your data.

[Kevin#]

a vba solution

Sub removeSubTotals()
Dim startRow As Long, lastRow As Long, lastCol As Long
Dim searchStr As String

With Sheets("Sheet1")
    startRow = 2
    lastRow = .Cells(startRow, 5).End(xlDown).Row
    lastCol = .Cells(startRow, 5).End(xlToRight).Column

For r = lastRow To startRow Step -1
        If Left(.Cells(r, 5).Value, 5) = "Total" Then
            searchStr = Mid(.Cells(r, 5).Value, 7)  '= text to match
        End If
        
        If .Cells(r, 5).Value = searchStr Then
            For c = 6 To lastCol
            Cells(r, c).Value = 0
            Next c
        End If
Next r
End With
End Sub

[jindon]

For the data provided.

Sub test()
    Dim x, e
    With Range("e1").CurrentRegion
        With .Columns(1)
            x = Filter(Evaluate("transpose(if(left(" & .Address & ",6)=""Total "",mid(" & _
                    .Address & ",7,100),char(2)))"), Chr(2), 0)
            x = Application.Match(x, .Cells, 0)
        End With
        For Each e In x
            .Rows(e).Offset(, 1).Resize(, .Columns.Count - 1).Value = 0
        Next
    End With
End Sub

[AB33]

If it starts with "Total", how come then rows which have total are not zeros? Try then the adjusted code.