How to delete rows with selected text?

U

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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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.

Code: 
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
 
Last edited:
Upvote 0
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: :confused:



<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>












MARK858 said:
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.

Code: 
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
Click to expand...
 
Upvote 0
So now you are aren't using Selection but the column name itself :rolleyes: what are all the words?
 
Upvote 0
All other words start on A down, move across to column L3000.

[TABLE="width: 1289"]
<tbody>[TR]
[TD][/TD]
[TD]SAMPLE DATA LISTED BELOW![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0002 CONDOMINIUM ASSOCIATION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]As of 1/31/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]xxxxxxxx, xx xxxxxx[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unit[/TD]
[TD]Resident[/TD]
[TD]Unit Address[/TD]
[TD]Status Date[/TD]
[TD][/TD]
[TD]Last Pay[/TD]
[TD]Last Pay[/TD]
[TD]Current[/TD]
[TD]Over 30[/TD]
[TD]Over 60[/TD]
[TD]Over 90[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD]Days[/TD]
[TD]Days[/TD]
[TD]Days[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









MARK858 said:
So now you are aren't using Selection but the column name itself :rolleyes: what are all the words?
Click to expand...
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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.





MARK858 said:
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.
Click to expand...
 
Upvote 0
Hopefully this makes sense.
Click to expand...

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.
 
Upvote 0
Is there no select case code for me to add to my VBA code, delete all these words, then delete rows.







MARK858 said:
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.
Click to expand...
 
Last edited:
Upvote 0
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
You must log in or register to reply here.

Similar threads

A
  • Solved
setting a range from Active cell to last cell when Active cell change columns
2
Replies
12
Views
608
jolivanes
jolivanes
T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
351
tixarah
T
M
  • Solved
VBA to Insert Mismatched Row in separate worksheet
2 3
Replies
21
Views
806
Peter_SSs
Peter_SSs
J
  • Question Question
VBA Error on Logic to Remove Rows based on Blank Cells
Replies
4
Views
378
Alex Blakenburg
A
S
  • Question Question
error on deleting last row
Replies
5
Views
369
Anthony47
A

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

Share this page

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