Delete Rows Shift Up Multiple Sheets

[amoverton2]

Hi,

I have the same data or keywords on multiple sheets. I want to put a keyword into a textbox on a userform, then click a command button, where it deletes the entire row if that keyword is found on multiple sheets then shift everything below it up at the same time.

I found this video on youtube but it works for one sheet, how can I make it so I can delete rows across all of my sheets or a selection of sheets:

I can do the above but I have 5 or 6 sheets and that's 5 or 6 buttons (one per sheet I want to delete). I want to combine it into one button (To rule them all!!!)

Thanks!
Adam

 

[My Aswer Is This]

If the script has a popup that says no value found. It means on that sheet it did not find the value. Just click ok and it will move on to next sheet

 

[amoverton2]

Try this:
This script should be put in a Button on your Userform
The Userform needs a Textbox named Textbox1
Put the search value in the Textbox then click the button with the script in it.
The script will search column B of all sheets in your workbook for the value you put in the Textbox

So if you put Alpha in the textbox the script will look in column B of all sheets and if if finds Alpha it will delete that row.
Now it looks for just Alpha if the value in the cell is Alpha Soup it will skip that row.

Private Sub CommandButton1_Click()
'Modified  9/28/2021  3:05:40 AM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Dim c As Long
Dim i As Long
Dim s As Variant
c = 2 ' Column Number Modify this to your need
s = TextBox1.Value 'Search Value Modify to your need
For i = 1 To Sheets.Count
    Lastrow = Sheets(i).Cells(Rows.Count, c).End(xlUp).Row

With Sheets(i).Cells(1, c).Resize(Lastrow)
[COLOR=rgb(0, 0, 0)][I]   [/I][/COLOR][B][COLOR=rgb(0, 0, 0)][I] .AutoFilter 1, s[/I][/COLOR][/B]
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Next
Application.ScreenUpdating = True
End Sub

I'm getting an error and the debugger points to this line:
.AutoFilter 1, s

Run-time error '1004':
AutoFilter method of Range class failed

 

[amoverton2]

I'm also getting an error that the command does not work on protected sheets....

also can the code be written to search just the following sheets:
E_ProspectiveGain_Add
E_AdminInfoGain_Add
E_LastContact_Add
E_TravelInfo_Add
E_FamilyInfo_Add
E_MiscNotes_Add

 

[My Aswer Is This]

I'm getting an error and the debugger points to this line:
.AutoFilter 1, s

Run-time error '1004':
AutoFilter method of Range class failed

This may be because you are not entering the value to search for in in a Textbox named Textbox1
You never told me the name of the Textbox so I used Textbox1

 

[My Aswer Is This]

I'm also getting an error that the command does not work on protected sheets....

also can the code be written to search just the following sheets:
E_ProspectiveGain_Add
E_AdminInfoGain_Add
E_LastContact_Add
E_TravelInfo_Add
E_FamilyInfo_Add
E_MiscNotes_Add

Well that is true a script cannot run on a Protected sheet. The purpose of protecting a sheet is so no one can do any thing without un protecting the sheet.

And you never mentioned not running the script on certain sheets.
It's difficult helping when we keep moving the Goal post. When we don't even have it working yet.

 

[amoverton2]

This may be because you are not entering the value to search for in in a Textbox named Textbox1
You never told me the name of the Textbox so I used Textbox1

I changed the type/name of the textbox to a combo box "cmbDELNAME" and I changed it in the code to reflect the new name (named range/rowsource, pulldown -- that way I'm not typing blind or have the option to pick from an established list) that's on me for the change... however, I didn't expect the protected sheet issue since all the other codes I have unprotect then reprotect after running, So i assumed, so that is also on me....

 

[My Aswer Is This]

I changed the type/name of the textbox to a combo box "cmbDELNAME" and I changed it in the code to reflect the new name and it just does the protected sheet error...

So your selecting the value to look for from a Combobox Name: "cmbDELNAME"
And your still saying the script does not run?
Like I said earlier the script will not run on protected sheets.
I have worked out a way to just search certain sheet names.
But are these sheet names unprotected?

 

[amoverton2]

So your selecting the value to look for from a Combobox Name: "cmbDELNAME"
And your still saying the script does not run?
Like I said earlier the script will not run on protected sheets.
I have worked out a way to just search certain sheet names.
But are these sheet names unprotected?

The script runs, but won't delete because of the sheet protection. Yes the sheet names listed are protected.

 

[My Aswer Is This]

The script runs, but won't delete because of the sheet protection. Yes the sheet names listed are protected.

I'm wasting my time here. The script will not run on protected sheets. If you want to run the script you need to unprotect these sheets.
So do you want my new script which only works on the sheets you mentioned?
No need for me to continue here if you will not unprotect those sheets

That is why people protect sheets to stop things from happening unless you know the password

 

[amoverton2]

I'm wasting my time here. The script will not run on protected sheets. If you want to run the script you need to unprotect these sheets.
So do you want my new script which only works on the sheets you mentioned?
No need for me to continue here if you will not unprotect those sheets

That is why people protect sheets to stop things from happening unless you know the password

Yes, I would like the script.