How to delete rows with selected text?

Unicode

Board Regular
Joined
Apr 9, 2019
Messages
58
Every other cell on column "B" has the following letters, "LLC". My vba script should clear all "LLC" and horizontally delete entire ROW. However I keep getting a compile error, is this due to the reason that I have not selected a range?
The code I have already used: :confused:



Sub deleteRowswithSelectedText()
For Each Cell In Selection
If Cell.Value = "Bad Row" Then
Rows(Cell.Row).ClearContents
End If
Next
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
End Sub
 
I propose the following code, with the ability to set range from A2:L300, delete an array of words. Perhaps, my theory is not easy to comprehend.



Sub DeleteMatchingCriteria()
Application.ScreenUpdating = False
Dim toDelete As Variant

' set the words to delete
toDelete = Array("As of 1/31/2019", "CA", "Unit")


Dim colD As Range
Set col = Sheet2.Range("A1:L" & Sheet2.Range("L" & Rows.Count).End(xlUp).Row)


With col
.AutoFilter Field:=1, Criteria1:=toDelete, Operator:=xlFilterValues
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With


Sheet2.AutoFilterMode = False
End Sub





There probably is but I have no list of words and no description that I can work out exactly what you want doing, just a table with no description on what is to be deleted or what not.

If you said I want the rows deleted where this specific list of words appear in these specific columns I might have some idea but now are are talking as if there are different things to do on different pages and I don't really want to take 15 posts to try and work it out.
 
Upvote 0

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Code:
Sub DelAL()
  Dim myVar As Variant, DelMe As Variant
  Application.ScreenUpdating = False
  
  DelMe = Array("As of 1/31/2019", "CA", "Unit")
  
  For Each myVar In DelMe
    Sheet2.Columns("A:L").Replace "*" & myVar & "*", "#N/A", xlWhole, , True, False, False
  Next
  
  On Error Resume Next
  Sheet2.Columns("A:L").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
  
  Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

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