VBA Delete Entire Row if Contains Certain Text

B

bigmacneb

Board Regular
Joined
Jul 12, 2005
Messages
93
I've searched on here, but every code I put in gives me an error back. Data in column D, If any of the cells contains "Record Only" I need it to delete the entire row.
Thanks
 
Sort by date Sort by votes
Hi,
Here is the full working code. It checks all the pages and deletes the text if found. However, If it does not find it then it gives the runtime error I mentioned in the previous post.


Private Sub Delete_Click()
'select sheet and delete data on sheet
Sheets("TACGASS").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("TACGLog").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("TACGFinish").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("TACGMach").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With

'select sheet and delete data on sheet
Sheets("TACGDie").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("BUHLER").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("ALDI").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("NAS").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("UFP").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("RINGForklift").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("FoamFab").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("SafeLite").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("Clerical").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("QC").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("Good but lack Exp").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With

End Sub
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Dsr122076 said:
Hi,
Here is the full working code. It checks all the pages and deletes the text if found. However, If it does not find it then it gives the runtime error I mentioned in the previous post.


Private Sub Delete_Click()
'select sheet and delete data on sheet
Sheets("TACGASS").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("TACGLog").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("TACGFinish").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("TACGMach").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With

'select sheet and delete data on sheet
Sheets("TACGDie").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("BUHLER").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("ALDI").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("NAS").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("UFP").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("RINGForklift").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("FoamFab").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("SafeLite").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("Clerical").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("QC").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With
'select sheet and delete data on sheet
Sheets("Good but lack Exp").Select
With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "Userform2.name.Text"
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error Resume Next

End With
.AutoFilterMode = False
End With

End Sub
Click to expand...
Had to make a change and label the name box :Name2
this is so that i can clear the text at the end of a succesful run. However I continue to get an error when the text is not found...
Also just figured out that it is not deleting the correct row. I was testing on cell a1 but when i added additional words in a1 and moved the test answer to a3 it would find it but still delete row a1.... so back to the drawing board
 
Upvote 0
If you want to loop through "ALL" sheets then setting sheet reference will not be necessary.

Test this on backup.
Code: 
Private Sub Delete_Click()
For i = 1 To Sheets.Count
    With Sheets(i)
    .AutoFilterMode = False
        With .Range("A1", .Range("A" & Rows.Count).End(xlUp))
        On Error Resume Next
        .AutoFilter 1, Userform2.Name2.Value
        .Offset(0).SpecialCells(12).EntireRow.Delete
        On Error GoTo 0
        End With
    .AutoFilterMode = False
    End With
Next i
End Sub
 
Upvote 0
taurean said:
If you want to loop through "ALL" sheets then setting sheet reference will not be necessary.

Test this on backup.
Code: 
Private Sub Delete_Click()
For i = 1 To Sheets.Count
    With Sheets(i)
    .AutoFilterMode = False
        With .Range("A1", .Range("A" & Rows.Count).End(xlUp))
        On Error Resume Next
        .AutoFilter 1, Userform2.Name2.Value
        .Offset(0).SpecialCells(12).EntireRow.Delete
        On Error GoTo 0
        End With
    .AutoFilterMode = False
    End With
Next i
End Sub
Click to expand...
It works except for that fact that it deletes all rows above the value that i want to delete. It deletes the one with the value as well.

The sheets will have many rows, but the information will always be in the first column. I only want it to erase the exact row that I need erased and not the ones above or below.
 
Last edited:
Upvote 0
Dsr122076 said:
It works except for that fact that it deletes all rows above the value that i want to delete. It deletes the one with the value as well.

The sheets will have many rows, but the information will always be in the first column. I only want it to erase the exact row that I need erased and not the ones above or below.
Click to expand...


It also deletes the first row on pages where it does not find a match.
 
Last edited:
Upvote 0
Hi dude,
Could you please help me with code for the following condition.,.

it should be like if the particular condition satisfies then the entire row should be deleted.,.

Can you please help me..

Mark F said:
Hi

Try the code below

Hope it works for you

Mark:)
Code: 
Sub DeleteRowWithContents()
'========================================================================
' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "Record Only" IN COLUMN D
'========================================================================
    Last = Cells(Rows.Count, "D").End(xlUp).Row
    For i = Last To 1 Step -1
        If (Cells(i, "D").Value) = "Record Only" Then
    'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
            Cells(i, "A").EntireRow.Delete
        End If
    Next i
End Sub
Click to expand...
 
Upvote 0
Hi, slightly off course but though best to add to here rather than start a new thread.

I am trying to start at the bottom of column B and search through the rows, bottom up, untill "total" is found.

When found, need to delete all the rows from 30 above, to 8 below the total row

I thought this would work, but doesn't. No error, just doesn't do anything. any help appreciated.
Code: 
Sub Test_DeleteRows()
FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Property Data").Activate
For x = FinalRow To 30 Step -1
If Cells(x, 2).Value = "Total" Then
Range(x + 8 & ":" & x - 30).Select
Selection.EntireRow.Delete
End If
Next x
End Sub
 
Upvote 0
Probably a new thread would be better.

However ...

Do you actually have "Total" occurring in ColumnB, rather than say "total", or with a non-printing character?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Delete entire rows with negative numbers in Amount Column - VBA
Replies
6
Views
364
Peter_SSs
Peter_SSs
D
  • Question
Find text in one cell and add data to the cell below it
Replies
7
Views
322
rlv01
R
N
  • Solved
Function to find a portion of text in a cell
Replies
5
Views
540
Fluff
Fluff
C
  • Question
VBA Copy Sort
Replies
4
Views
159
Keebler
Keebler
C
  • Solved
Lookup certain value in row and give back cell value in same column first row
Replies
7
Views
392
Fluff
Fluff

Forum statistics

Threads
1,215,327
Messages
6,124,287
Members
449,149
Latest member
mwdbActuary

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