Argthtjtr

In the posted example, if for Jane and David I'm only interested in rows with "Total" in column B, how do I remove the rows that do not contain "Total" in column B. I would like to apply this functionality to multiple sheets within a workbook.

Your simplest approach is to simply sort on the value you want and delete all the rows before that value, then all the ones after it which will leave only the rows with exactly it.

Of course, this being Excel, you can do each little bit of that slightly differently with wee bit more work. For instance, insert a "helper" column somewhere convenient (right next to your column of interest for example) and quickly write an IF test for the value with "" for failure to find it. Then copy, or fill, the entire column with the formula (each testing that row's cell), sort on the helper column and all the rows you want to delete are together rather than in two bunches. Which of your various options seems easier in your circumstance and preference is the way you might go.

If you need a macro to do it, follow the same concept. In the macro simply test the first cell in your column of interest for the value and if it is NOT found have the macro delete that row, then loop until you reach the end of data. If no cell can ever be blank in that column, you can test for blankness and quit when found. But... if a cell could be empty, you will need to try something else. Any of a variety of things could work depending upon your table and the other cells' characteristics, but you could also just start the macro on the last possible cell, chosen by your brain, and work upward (including a test for the row number each time so you can stop at, say, row 2 i fyou have headers rather than deleting our header row and then permanently looping on the first row).

Or combine the two. Sort the data on that column, in a macro, look for the first occurence of the desired value, delete all the DATA rows above that value, then test until the first failure, and delete all the rows afterwards.

So many ways. My approach would be the very first one for my own working. For a spreadsheet I'm giving to someone else to use, I'd modify that by trying to automate for him. Or... depends on the user, knowledgeable vs. newbie, boss vs. Jimmy from janitorial. That kind of thing.

Thank you all for your help. I ended up doing the following:

1. Use conditional formatting to highlight all cells with duplicate names


2. Sort cells by colour, then sort by name of apple


3. Shift select all rows containing a highlighted cell, that did not have "Total" in the apple column.

This process has been relatively quick for large datasets.

I would like to suggest a MACRO, which the best and fastest method to Delete all rows from all the Sheets, doesn't contains text TOTAL in specific Column B.

Sub Delete_Rows()



Dim rng As Range, cell As Range, del As Range

Dim sht As Worksheet



For X = 1 To 10

    Set sht = Sheets(X)

    Set del = Nothing



    Set rng = Intersect(sht.Range("B1:B9"), sht.UsedRange)



    For Each cell In rng.Cells



    If (cell.Value) <> "Total" Then

        If del Is Nothing Then

            Set del = cell

        Else

            Set del = Union(del, cell)

        End If

    End If



    Next cell



    If Not del Is Nothing Then del.EntireRow.Delete

Next X



End Sub

How it works:

• At any Sheet Press Alt+F11 to open VB Editor then Copy & Paste this Code as Standard Module.


• Before you RUN the Macro, insure that the Search String Total is available in Range B1:B9 in all the Sheets, from where you are
  trying to Delete Rows.

Note:

• For X = 1 to 10 indicated that MACRO will search TOTAL in 10 Worksheets, which is editable.




• You may also adjust/alter the Search Range B1:B9 and the String Total as you need.