VBA to delete rows from table that do not contain specific text

[gberg]

I am looking for a VBA code to delete rows from a table that do not contain an "M". The information is in the fourth column (Column D) and has a header of "Category". There are five options for what will be in Column D; "M", "S", "E", "L", "O". I want to delete any row that does not contain "M". I have a solution that runs a loop but there could be thousands of lines and this loop gets bogged down.

Any Suggestions?

 

[Marc L]

​

Different ways but as it depends on the worksheet design so I can't help without an attachment​

but you can already see the many threads about this common subject …​

 

[gberg]

I tried looking but could not find an appropriate thread, I must have poor search skills

Here is an example of the table

Acct ID	Section #	Section Description	Category	Cost Code	Cost Code Description	Account Code	Account Code Description	Quantity	UOM
71D0001	9500​	Acoustical Ceilings	M	FASTENERS	Fasteners	MT114	FASTENERS	666.73​	EA
71D0001	9500​	Acoustical Ceilings	M	FASTENERS	Fasteners	MT114	FASTENERS	787.96​	EA
71D0001	9500​	Acoustical Ceilings	L	ACHW	AC - Grid Hanger Wires	12420​	AC - Hanger Wire	606.12​	EA
71D0001	9500​	Acoustical Ceilings	M	AC	Acoustical Ceilings	MT100	ACOUSTICAL CEILING	528.22​	EA
71D0001	9500​	Acoustical Ceilings	M	AC	Acoustical Ceilings	MT100	ACOUSTICAL CEILING	294.25​	LF
71D0001	9500​	Acoustical Ceilings	M	AC	Acoustical Ceilings	MT100	ACOUSTICAL CEILING	4576.91​	LF
71D0001	9500​	Acoustical Ceilings	M	AC	Acoustical Ceilings	MT100	ACOUSTICAL CEILING	544.06​	LF
71D0001	9500​	Acoustical Ceilings	M	AC	Acoustical Ceilings	MT100	ACOUSTICAL CEILING	184.82​	SF
71D0001	9500​	Acoustical Ceilings	M	AC	Acoustical Ceilings	MT100	ACOUSTICAL CEILING	9513.09​	SF
71D0001	9500​	Acoustical Ceilings	L	ACANGLESHADOW	AC - Angle Shadow	12422​	AC - Wall Angle	2289.23​	LF
71D0001	9500​	Acoustical Ceilings	L	ACGRID24X24	AC - Grid 24" x 24"	12401​	AC - Grid	2816.33​	SF
71D0001	9500​	Acoustical Ceilings	L	ACACCESSPERIMETERTRIM	AC Accessories - Perimeter Trim	12436​	AC - Edge Trim (Axiom or Compasso)	529.09​	LF
71D0001	9500​	Acoustical Ceilings	L	ACGRIDMISCEA	AC - Grid Misc EA	12490​	AC - Misc EA	111​	EA
71D0001	9500​	Acoustical Ceilings	L	ACGRIDMISCLF	AC - Grid Misc LF	12494​	AC - Misc LF	231.15​	LF

I only want to be left with "M" in the "Category" column

 

[Marc L]

​

So difficult to see without a real attachment : is it a true Excel table or just a range of cells ?​

In case of an Excel table you can use a files hosting website and link a workbook saved in binary mode (.xlsb) …​

 

[gberg]

​

So difficult to see without a real attachment : is it a true Excel table or just a range of cells ?​

In case of an Excel table you can use a files hosting website and link a workbook saved in binary mode (.xlsb) …​

I have no idea how to do that

 

[Marc L]

​

And the answer to the question is …​

 

[Fluff]

How about

Sub gberg()
   With ActiveSheet
      .Range("A1:J1").AutoFilter 4, "<>M"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

 

[Marc L]

Hi Fluff,​

​

as the OP has stated for 'thousands of rows' I hope there are not to much non contiguous rows to delete or like this it may need a while …​

​

 

[Fluff]

It will still be faster than looping & deleting rows individually.

 

[Marc L]

Yes obviously …​