How to delete rows with selected text?

[Unicode]

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:



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

 

[MARK858]

Why do you need to clear the cells first if you are deleting them?
Even though there are much faster methods the code below deletes rows where the entire cell contents are "Bad Row" (as per your code rather than the LLC in your question) if you select a range that is within a single column.

Sub deleteRowswithSelectedText()
    Dim x As Long, y As Long, MyRow As Long
    
    x = Selection.Rows(1).Row
    y = Selection.Rows.Count + x - 1

    Application.ScreenUpdating = False

    For MyRow = y To x Step -1
        If Cells(MyRow, Selection.Column).Value = "Bad Row" Then Cells(MyRow, Selection.Column).EntireRow.Delete
    Next

    Application.ScreenUpdating = True
End Sub

 

[Unicode]

Thank you for the question. I managed to get another update with the coding, however, besides the text "Bad Row" I have more rows with other text to remove, due to the reason that the output runs 6-7 pages on Excel sheet, I am trying to now include the other words and dates on other cells for me to remove in block an donly remain with needed report information. Below code update:



<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Dim lr As Long
Dim i As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = lr - 1 To 2 Step -1
If Cells(i, "B") = "Bad Row" Then
Cells(i, "B").EntireRow.Delete
End If
Next i</code>

Why do you need to clear the cells first if you are deleting them?
Even though there are much faster methods the code below deletes rows where the entire cell contents are "Bad Row" (as per your code rather than the LLC in your question) if you select a range that is within a single column.

Sub deleteRowswithSelectedText()
    Dim x As Long, y As Long, MyRow As Long
    
    x = Selection.Rows(1).Row
    y = Selection.Rows.Count + x - 1

    Application.ScreenUpdating = False

    For MyRow = y To x Step -1
        If Cells(MyRow, Selection.Column).Value = "Bad Row" Then Cells(MyRow, Selection.Column).EntireRow.Delete
    Next

    Application.ScreenUpdating = True
End Sub

 

[MARK858]

So now you are aren't using Selection but the column name itself what are all the words?

 

[Unicode]

All other words start on A down, move across to column L3000.

	SAMPLE DATA LISTED BELOW!
			0002 CONDOMINIUM ASSOCIATION
				As of 1/31/2019
	xxxxxxxx, xx xxxxxx
Unit	Resident	Unit Address	Status Date		Last Pay	Last Pay	Current	Over 30	Over 60	Over 90	Balance
					Date	Amount		Days	Days	Days

<tbody>
</tbody>

So now you are aren't using Selection but the column name itself what are all the words?

 

[MARK858]

The words that are in column B that are being searched for the rows to be deleted, a list please or if a totally new requirement please describe what you want done in exact detail.

 

[Unicode]

From my last post, it would have been ideal if all words would have been on column B, however, Sheet1, includes 6-7 pages on Sheet1. For example, after page1, the same words list from range A:L, then all needed information lists, then down the rows Page2, 3,4, 5 continue on Sheet1. I would like to clear and delete rows that have additional words after Page1. Hopefully this makes sense.

The words that are in column B that are being searched for the rows to be deleted, a list please or if a totally new requirement please describe what you want done in exact detail.

 

[MARK858]

Hopefully this makes sense.

Nope, I see nothing specific listed to base the delete on (and still no reason for the clear before the delete) and so I think I will leave this post for someone more intelligent than me to decipher.

 

[Unicode]

Is there no select case code for me to add to my VBA code, delete all these words, then delete rows.

Nope, I see nothing specific listed to base the delete on (and still no reason for the clear before the delete) and so I think I will leave this post for someone more intelligent than me to decipher.

 

[MARK858]

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.