Deleting Rows That Contain Certain Text

Redwinger354

New Member
Joined
Aug 22, 2007
Messages
5
Hello All! :biggrin:

I have a spreadsheet nearing 10000 various products with columns A-M.
I need to do the following:

Delete the entire row and shift up
If column H contains "this text" or "File Server Manager" or "ABT". Column H being the product description.

Basically, I have customers that are authorized only to sell certain products. So instead of filtering threw and saying, ok he cant sell "File Server Manager" delete that entire row. OR he cant sell these 10 products cause it says oracle in the product description then delete that row.

I was researching the fourms and saw something on AutoFilter. Will that work?

It was something along the lines of:

.AutoFilter field:=1, Criteria1:="ENTER TEXT HERE"
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete

I appreciate all your help!

Sincerely,
James
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi James,

Here's the code-free version.

In a blank column to the right (say, Z) we can insert some criteria to use with Advanced Filter. Reason: It's easier to use than AutoFilter if you have multiple criteria for a single field. Assuming your headings are in Row 1, do this:

1. Z1 has the same text as the heading for column H.
2. Z2, Z3, Z4 .... have the individual criteria that you want to filter for: this text, File Filter Manager, ABT, etc.
3. Select all the data in column H and Data | Filter | Advanced Filter.
4. Make sure the data range is Column H only; for the Criteria range, select the data in Column Z (just the values, not blanks, unless you want to include blanks in the filter); choose to Filter In Place, then OK.
5. Select the first visible cell in column A BELOW the headings, then CTRL+SHIFT+LEFT and CTRL+SHIFT+DOWN to select the data in the table.
6. Edit | GoTo | Special and select Visible Cells Only.
Edit | Delete and select Entire Row
7. Data | Filter | Show All Data

Obviously, that can be done in code too but that's overkill if you only need to run the process once.

Denis
 
Upvote 0
Hello Dennis,

Thanks! I will give this a shot.

I do have to actually run 36 differnet versions of this once a month. If that is the case would code be something to consider?

Thanks again!
James
 
Upvote 0
Hi James,

Yes, if you run it regularly then code may be the way to go. But some questions first.

1. You mentioned that you run it 36 times. Is that in different files, or 36 different entry types that require culling in Column H?

2. Are the conditions going to change on a regular basis, or will the exclusions always be the same core set? If not, will you have different sets of exclusions for different users?

The reason I ask, is that all of the above will affect how the file is set up. The aim is to avoid hard-wiring options that will change, and are best picked up from a table in the workbook.

Denis
 
Upvote 0
Hello Denis,

1. There is one data file with all of our products and 36 different partners. I always start with the orginal data file and then customize it for each partner. Each partner is authorized to sell a certain set of products. Partner A can sell Widget 3,5 & 7 and Partner B can sell Widget 5,7 &9. I always go through column H because it is easiest to pick out KeyWords that differentiate the products. I can set certain keywords to delete the products I need to delete for that specific partner.

2. If by conditions you mean the keywords in which i choose then yes they may. But once i have that set of keywords for Partner A then that set wont change very often. Only If new products are added to the list and I have to delete the new products.

I hope this helps. Im trying not to confuse you! :biggrin:

Sincerely,
James
 
Upvote 0
Hi James,

There's a few things you could do to simplify coding the process.

1. Have a main list on a sheet that gets updated as items are added
2. Copy this data to each partnter sheet so that partner-specific deletions don't upset the main list
3. A third page where you maintain a table of partners and the items to remove

The layout for the lookup table would be something like:

Excel Workbook
ABCDEF
1Partner1Partner2Partner3Partner4Partner5Partner6
2Item1Item5Item10Item12Item16Item17
3Item2Item6Item11Item13Item18
4Item3Item7Item14Item19
5Item4Item8Item15Item20
6Item9
Lookup Sheet


Note: Make the names in the top row of the table match the sheet names for each partner!

On each partner sheet the data and the filter criteria would be something like:
Excel Workbook
ABCDEFGHIJKL
11Heading1Heading2Heading3Heading4Heading5Heading6Heading7KeywordHeading9Heading10Keyword
12DataDataDataDataDataDataDataItem1DataDataItem5
13DataDataDataDataDataDataDataItem2DataDataItem6
14DataDataDataDataDataDataDataItem3DataDataItem7
15DataDataDataDataDataDataDataItem4DataDataItem8
16DataDataDataDataDataDataDataItem5DataDataItem9
17DataDataDataDataDataDataDataItem6DataData
18DataDataDataDataDataDataDataItem7DataData
19DataDataDataDataDataDataDataItem8DataData
Partner Sheet


The code needs to loop through the first row of the table and...
1. Copy the main data to the sheet whose name matches the current cell
2. Copy the items in the lookup table to the criteria list in the partner sheet
3. Run the filter and delete the visible rows

That will go into the next post...

Denis
 
Upvote 0
Here's the code.

Setup:
Main data table lives on a sheet called Main
Partner table lives on a sheet called Filters
At the top of the code you have the option to create the partner sheets from scratch if they don't already exist. That's commented out at the moment; instead, existing sheets are cleared.

Put this code in a new module (Alt+F11, then Insert | Module, Paste, and Alt+Q to return to Excel).
To run the code press Alt+F8 and double-click the macro name

Code:
Sub ProcessSheets()
    Dim c As Range
    Dim ShSource As Worksheet, _
        ShDest As Worksheet
    Dim Rng As Range, _
        RngData As Range
    
    Application.Screenupdating = False
    Set ShSource = Sheets("Filters")
    ShSource.Activate
    Range(Range("A1"), Range("A1").End(xlToRight)).Select
    For Each c In Selection
        '**initial setup section
        'to create new sheets, use this
'        ActiveWorkbook.Sheets.Add before:=Sheets("Filters")
'        ActiveSheet.Name = c.Value
        'to clear contents of existing sheets, use this
        Sheets(c.Value).Range("A1").CurrentRegion.Clear
        
        '**copy and process
        Set ShDest = Sheets(c.Value)
        'copy main data and transfer criteria
        Sheets("Main").Range("A1").CurrentRegion.Copy _
            Destination:=ShDest.Range("A1")
        Range(c, c.End(xlDown)).Copy _
            Destination:=ShDest.Range("P1")
        With ShDest
            .Range("P1").Value = .Range("H1").Value
        End With
        'filter and clean up
        ShDest.Activate
        Set RngData = Range("A1").CurrentRegion
        Set Rng = Range("P1").CurrentRegion
        
        Application.CutCopyMode = False
        RngData.AdvancedFilter _
            Action:=xlFilterInPlace, _
            CriteriaRange:=Rng, _
            Unique:=False
        RngData.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        ActiveSheet.ShowAllData
        Range("P:P").Clear
    Next c
    Application.Screenupdating = True
End Sub

Edited a typo in the code ...

Denis
 
Upvote 0
Denis,

Sorry for the delayed response. Just wanted to thank you. Im looking through everything now trying to break it down. I will let you know how everything goes.

Sincerely,
James
 
Upvote 0
Denis,

Im reading through everything and I beleive I made a typo and I wanted to verify that the method in play would still work.

When i said this "There is one data file with all of our products and 36 different partners." The partners are not within the data file. Its just the products.

Let me know if this changes anything.

Also i know this may be frustrating but im somewhat confused on what im suppose to do. :)

Also if the product description column changed would i change this line
Range("P1").Value = .Range("H1").Value ???

Thanks for your help.
Sincerely,
James
 
Upvote 0
Hi James,
When i said this "There is one data file with all of our products and 36 different partners." The partners are not within the data file. Its just the products.
A few questions to clarify:
1. Do you have a list of partners somewhere, with the exclusions? And would it matter if they were also listed in the product file?
2. Does the product file get re-created regularly (eg, as a download from some other source) or is it updated without being re-created?

If the file gets re-created, my approach will not work in its present form. If not, and the partner list can be placed in the same file, it greatly simplfies the processing. Once the new lists are created, each sheet can be moved to a new file so that partners only get their data.

Also if the product description column changed would i change this line
Range("P1").Value = .Range("H1").Value ???
Yep. If the product column moved to K (for example), you would change this to
Code:
Range("P1").Value = .Range("K1").Value

Also i know this may be frustrating but im somewhat confused on what im suppose to do
If you look at the first post I sent with the sheet layout, copy that layout.
Place the code into a new code module (Alt+F11, Insert>Module, Paste, then Alt+Q to return to Excel)
To run the code press Alt+F8, and double-click the macro name.

Hope this helps
Denis
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top