Excel Macro Suggestions - Delete All Rows That Comprise A Particular Textual content String

 This text will share an Excel Macro tip that may present you how one can delete all rows in your Excel sheet that include a selected textual content string.

This can be a query I get requested fairly repeatedly, and it's a actually helpful piece of code to know. The most typical technique to deal with that is to loop by way of your entire column, and if a cell within the column accommodates the required textual content string then to delete the entire row. Seeing as Excel will shift the rows upwards by default on deletion, then probably the most logical manner to do that is begin on the backside and work upwards.

This looping is sort of a sluggish course of and works for smaller information units as it's time consuming and sluggish to execute, however not so good on bigger information units, even with the Utility Objects ScreenUpdating and Calculation properties set to Guide for the next causes:-

1. The Delete Row command triggers Excel to recalculate, which if there are loads of formulation in your worksheet is time consuming.
2. Looping by way of the entire cells in a column and even the used cells in a column can also be time consuming.

So let's assume we've got the bigger information set, and wish to perform this process in a extra environment friendly manner. Effectively, I select to make use of the Auto filter Methodology, which assumes our information set is about up in a sure manner, with the primary row containing discipline headers or column names.

What Does The Macro Do?

This Macro will determine and filter the entire rows that include the textual content string your specify, then delete them.

How Does It Work?

FIRST. Open Visible Fundamental - by hitting F11 or Developer Tab - Visible Fundamental - Insert Menu - Module

Step 1. We specify we're utilizing the Energetic Work sheet, to make sure no different worksheet is affected by our actions, and at this stage the Auto Filter is first turned off (in case it's on) by Excel.

Step 2. The Filter is utilized to our information vary which is about to Column A, all the way down to the final row with information.

Step 3. We now are filtered on any rows that include "Whole", in Column A, (or Column 1)

Step 4. Any rows now which might be seen within the filtered information are deleted, utilizing the SpecialCells(12).EntireRow.Delete however we've got specified that we're offsetting the deletion by 1 cell, that is to accommodate the sphere or column headers which we don't wish to delete.

Step 5. Lastly the auto filter is eliminated as soon as once more.

Step 6. Take a look at Your Macro!!

Right here is the VBA coding must you wan tp copy and paste it into your Excel workbook.

Sub DeleteRowsMeetingCriteria()

With ActiveSheet ('STEP1)

.AutoFilterMode = False c

With Vary("A1", Vary("A" & Rows.Depend).Finish(xlUp))

.AutoFilter 1, "Whole" ('STEP3)

On Error Resume Subsequent

.Offset(1).SpecialCells(12).EntireRow.Delete ('STEP4)

Finish With

.AutoFilterMode = False ('STEP5)

Finish With

Finish Sub