Delete Rows Shift Up Multiple Sheets

amoverton2

Board Regular
Joined
May 13, 2021
Messages
50
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
18,163
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

amoverton2

Board Regular
Joined
May 13, 2021
Messages
50
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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

Board Regular
Joined
May 13, 2021
Messages
50
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
18,163
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
18,163
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
May 13, 2021
Messages
50
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
18,163
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
May 13, 2021
Messages
50
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
18,163
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
May 13, 2021
Messages
50
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,363
Messages
5,746,268
Members
424,002
Latest member
anon341

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
Top