Excel Macro Tips - Delete All Rows That Contain A Specific Text String

This article will share an Excel Macro tip that will tell you the best way to erase all lines in your Excel sheet that contain a particular content string.

This is an inquiry I get posed normally, and it is an extremely valuable bit of code to know. The most widely recognized approach to handle this is to circle through the whole section, and on the off chance that a phone in the segment contains the predetermined content string, at that point to erase the entire line. Seeing as Excel will move the columns upwards as a matter of course on erasure, at that point the most intelligent approach to do this is begin at the base and work upwards.

This circling is a significant moderate procedure and works for littler informational collections as it is tedious and moderate to execute, yet not all that great on bigger informational indexes, even with the Application Objects ScreenUpdating and Calculation properties set to Manual for the accompanying reasons:-

The Delete Row order triggers Excel to recalculate, which if there are a ton of recipes in your worksheet is tedious.

Circling through the majority of the cells in a section or even the utilized cells in a segment is additionally tedious.

So how about we expect we have the bigger informational index, and need to do this system in a progressively proficient manner. All things considered, I utilize the Auto channel Method, which accept our informational collection is set up with a particular goal in mind, with the main line containing field headers or section names.

What Does The Macro Do?

This Macro will distinguish and channel the majority of the columns that contain the content string your determine, at that point erase them.

How Can It Work?

FIRST. Open Visual Basic - by hitting F11 or Developer Tab - Visual Basic - Insert Menu - Module

Stage 1. We indicate we are utilizing the Active Work sheet, to guarantee no other worksheet is influenced by our activities, and at this stage the Auto Filter is first killed (on the off chance that it is on) by Excel.

Stage 2. The Filter is connected to our information go which is set to Column A, down to the last line with information.

Stage 3. We currently are sifted on any lines that contain "Aggregate", in Column An, (or Column 1)

Stage 4. Any lines now that are obvious in the separated information are erased, utilizing the SpecialCells(12).EntireRow.Delete however we have indicated that we are balancing the cancellation by 1 cell, this is to suit the field or section headers which we would prefer not to erase.

Stage 5. At long last the auto channel is expelled indeed.

Stage 6. Test Your Macro!!

Here is the VBA coding should you wan tp reorder it into your Excel exercise manual.

Sub DeleteRowsMeetingCriteria()

With ActiveSheet ('STEP1)

.AutoFilterMode = False c

With Range("A1", Range("A" and Rows.Count).End(xlUp))

.AutoFilter 1, "Absolute" ('STEP3)

On Error Resume Next

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

End With

.AutoFilterMode = False ('STEP5)

End With

End Sub

No comments:

Powered by Blogger.