VBA Delete Entire Row if Contains Certain Text

[bigmacneb]

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

 

[AEkass]

I'm brand new to using VBA and Macros. I am trying to delete rows based on column A and have yet to find code that works for me. My column A contains a 6-digit code (currently stored as general), and I need to delete rows in which the first 3 digits of the code contain a zero (examples: 120000, 100000), and codes in which the 4th digit contains anything but a zero (example: 121400).

This is the code I have been trying to work with (which is from a previous post)
Sub test()
With ActiveSheet
.AutoFilterMode = False
With Range("A1", Range("A" & Rows.Count).End(xlUp))
.AutoFilter 1, "110000"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

I'm pretty sure I need to change the "110000" part to a more complex statement, but am not sure what exactly to change it to.

Any advice would be much appreciated!

 

[chrisguk]

Why loop ?
Try AutoFilter ...

Sub test()
With ActiveSheet
    .AutoFilterMode = False
    With Range("d1", Range("d" & Rows.Count).End(xlUp))
        .AutoFilter 1, "*Record Only*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With
End Sub

Great method works perfect!!!

 

[taurean]

I'm brand new to using VBA and Macros. I am trying to delete rows based on column A and have yet to find code that works for me. My column A contains a 6-digit code (currently stored as general), and I need to delete rows in which the first 3 digits of the code contain a zero (examples: 120000, 100000), and codes in which the 4th digit contains anything but a zero (example: 121400).

This is the code I have been trying to work with (which is from a previous post)
Sub test()
With ActiveSheet
.AutoFilterMode = False
With Range("A1", Range("A" & Rows.Count).End(xlUp))
.AutoFilter 1, "110000"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

I'm pretty sure I need to change the "110000" part to a more complex statement, but am not sure what exactly to change it to.

Any advice would be much appreciated!

Are the two connected conditions or separate conditions?
i.e. Delete ONLY if both Cond1(First 3 characters have 0) & Cond2(4th character is non zero) meet.
OR
Delete if any of these conditions meet?

 

[jamesilius]

Hi,

I have a similar issue. I wonder if someone could help me with the VBA scripting for the below scenario:

I have a file that in column A has cells with the dropdown list with: "Yes", "No", "Remove". I would like the macro, when "Remove" is chosen, to display MsgBox "Would you like to delete this person?" with Yes/No options. If vbYes is selected to delete the row with a chosen person if vbNo to leave Sub.

I have started with the following:

Sub delete_RemoveCell()
Dim komorka As String

If (komorka.Value) = "*Remove*" Then
If MsgBox("Would you like to delete this person?", vbYesNo) = vbYes Then
Target.EntireRow.Delete
Else
Exit Sub
End If
End If

End Sub

Thank you in advance for your assitance

 

[taurean]

Right Click on Sheet Tab and choose option "View Code" and paste this code in the Code pane.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
Application.EnableEvents = [color=darkblue]False[/color]
[color=darkblue]If[/color] Target.Column = 1 And Target.Value = "Remove" [color=darkblue]Then[/color] [color=green]'Checking only in column A[/color]
    [color=darkblue]If[/color] MsgBox("Would you like to delete this person?", vbYesNo) = vbYes [color=darkblue]Then[/color]
    Target.EntireRow.Delete
    [color=darkblue]End[/color] [color=darkblue]If[/color]
[color=darkblue]End[/color] [color=darkblue]If[/color]
Application.EnableEvents = [color=darkblue]True[/color]
End [color=darkblue]Sub[/color]

 

[jamesilius]

You are the best thank you

 

[Waqar]

Hi

I want also this but it does not work for me.......

If Specific text appear in coloumn D entire Row delete and paste onanother sheet

Please Elobrate.....

Regards

 

[taurean]

Hi

I want also this but it does not work for me.......

If Specific text appear in coloumn D entire Row delete and paste onanother sheet

Please Elobrate.....

Regards

Waqar,

In above case, its data validation and the result will be deletion of row.

Can you explain your situation please?
i.e. How do you want the macro to run? Using macro dialog box or Based on worksheet's change.

Your references (copy to sheet name etc.)....

So you will have to elaborate

 

[Dsr122076]

Hi Guys new to the forum. I am making a command button on my VB project. Its only function would be to search each of the sixteen pages of the workbook for a text value which would be determined by the user in a text box. Once entered they should only need to press the command button and it would search all the worksheets and delete any row that contains the value.

additional info:
form id: UserForm 2
Textbox name : Name
Column containing value: column 1 or A in all worksheets.


So far I have:
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
End Sub


This succeeds in deleteing the rows if the value is present on the active sheet. however if there is no value it locks up to a debug error. What I would like it to do, is move on to the next sheet if no additional entries are found on that active sheet. I can continue the code once I can make it move on to the following sheet. I just cannot get past the debug. It highlights this: .AutoFilter 1, "Userform2.name.Text" when it does not find any entries. When it does find it it works perfectly. Well excpet for the part that it doesnt move on to the next sheet yet.

 

[Dsr122076]

Hi Guys new to the forum. I am making a command button on my VB project. Its only function would be to search each of the sixteen pages of the workbook for a text value which would be determined by the user in a text box. Once entered they should only need to press the command button and it would search all the worksheets and delete any row that contains the value.

additional info:
form id: UserForm 2
Textbox name : Name
Column containing value: column 1 or A in all worksheets.


So far I have:
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
End Sub


This succeeds in deleteing the rows if the value is present on the active sheet. however if there is no value it locks up to a debug error. What I would like it to do, is move on to the next sheet if no additional entries are found on that active sheet. I can continue the code once I can make it move on to the following sheet. I just cannot get past the debug. It highlights this: .AutoFilter 1, "Userform2.name.Text" when it does not find any entries. When it does find it it works perfectly. Well excpet for the part that it doesnt move on to the next sheet yet.

New info: I get Runtime error '1004' AutoFilter of range class failed
This happens ONLY when it does not find the inforamtion I am trying to delete. When it finds it, it works fine.