Delete Rows When Word Is Found

Dazzawm

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,647
Office Version
  1. 365
Platform
  1. Windows
Hi I need a macro that will look in the column I select for a specific word that could be amongst other text then delete the entire row. Also I need it to delete any blank rows that are directly beneath until it finds the next cell with data in.

i.e it looks for the word 'Hello' (maybe amongst other text) in the active column which the first may be in C12 the next 6 rows in column C may be empty so those rows need to be deleted also until the next data is found and so on..
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Try

Code: 
Sub Daz()
Dim s As String, iCol As Long, LR As Long, i As Long
iCol = ActiveCell.Column
LR = Cells(Rows.Count, iCol).End(xlUp).Row
s = LCase(InputBox("Enter word to look for"))
For i = LR To 1 Step -1
    If LCase(Cells(i, iCol).Value) Like "*" & s & "*" Or Cells(i, iCol).Value = "" Then Rows(i).Delete
Next i
End Sub
 
Upvote 0
That hasnt worked that has just deleted evey row that does not have data in D, after selecting D as the column and entering the word in the box.
 
Upvote 0
You said that you wanted rows with empty cells in the selected column to be deleted :confused:
 
Upvote 0
ADVERTISEMENT
Not every row that has empty cells in D, just the rows directly beneath when a word is found. The first row in D with hello in may be 12 so every row which is blank beneath row 12 will be deleted until the next data is found in D which may be row 17 but that may not have hello in it so it needs to look for the next row that has hello in then delete that row and the blank rows directly beneath and so on..
 
Upvote 0
I Have highlighted in yellow what rows would be deleted if the active column is D looking for the word hello.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Data</TD><TD> </TD><TD> </TD><TD> </TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Data</TD><TD> </TD><TD> </TD><TD> </TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Data</TD><TD> </TD><TD> </TD><TD> </TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Data</TD><TD> </TD><TD> </TD><TD> </TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Data</TD><TD> </TD><TD> </TD><TD> </TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Data</TD><TD> </TD><TD> </TD><TD> </TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Data</TD><TD> </TD><TD> </TD><TD> </TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Hello</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Goodbye</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD>Data</TD><TD> </TD><TD> </TD><TD> </TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD>Data</TD><TD> </TD><TD> </TD><TD> </TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD>Data</TD><TD> </TD><TD> </TD><TD> </TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Hello</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD><TD style="BACKGROUND-COLOR: #ffff00">Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD><TD>Data</TD></TR></TBODY></TABLE>
 
Upvote 0
ADVERTISEMENT
This worked with your example

Code: 
Sub Daz()
Dim s As String, iCol As Long, LR As Long, i As Long
Dim r As Range, F As Boolean
iCol = ActiveCell.Column
LR = Cells(Rows.Count, iCol).End(xlUp).Row
s = LCase(InputBox("Enter word to look for"))
For i = 1 To LR
    If LCase(Cells(i, iCol).Value) Like "*" & s & "*" Then
        If r Is Nothing Then
            Set r = Cells(i, iCol)
        Else
            Set r = Union(r, Cells(i, iCol))
        End If
        F = True
    ElseIf Cells(i, iCol).Value <> "" Then
        F = False
    Else
        If F Then
            If r Is Nothing Then
                Set r = Cells(i, iCol)
            Else
                Set r = Union(r, Cells(i, iCol))
            End If
        End If
    End If
Next i
If Not r Is Nothing Then r.EntireRow.Delete
End Sub
 
Upvote 0
Thanks thats worked. What if I want to look for more than one word do I just keep repeating the process or can a put a comma between each one in the box?
 
Upvote 0
I think repeat. This code will loop until you click cancel

Code: 
Sub Daz()
Dim s As String, iCol As Long, LR As Long, i As Long
Dim r As Range, F As Boolean
iCol = ActiveCell.Column
s = "£"
Do While s <> ""
    s = LCase(InputBox("Enter word to look for"))
    If s = "" Then Exit Sub
    LR = Cells(Rows.Count, iCol).End(xlUp).Row
    For i = 1 To LR
        If LCase(Cells(i, iCol).Value) Like "*" & s & "*" Then
            If r Is Nothing Then
                Set r = Cells(i, iCol)
            Else
                Set r = Union(r, Cells(i, iCol))
            End If
            F = True
        ElseIf Cells(i, iCol).Value <> "" Then
            F = False
        Else
            If F Then
                If r Is Nothing Then
                    Set r = Cells(i, iCol)
                Else
                    Set r = Union(r, Cells(i, iCol))
                End If
            End If
        End If
    Next i
    If Not r Is Nothing Then r.EntireRow.Delete
    Set r = Nothing
    F = False
Loop
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

Dazzawm
  • Solved
Delete any rows with data in once specific word is found.
Replies
7
Views
138
Joe4
Joe4
T
  • Solved
Automate deleting rows containing a specific word in a specific column
2
Replies
12
Views
459
tompepper
T
A
  • Question
Automated Row Deletion
Replies
1
Views
104
James006
James006
M
  • Solved
ActiveCell two rows below last word in a column
Replies
6
Views
118
mlarson
M
A
  • Solved
Delete rows based on not equal to with wild card
Replies
4
Views
152
Apple08
A

Forum statistics

Threads
1,196,105
Messages
6,013,489
Members
441,767
Latest member
Craigh4444

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