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]

I did not look at the video.
So give me some specific details

1. What is the name of the Textbox on the userform where you plan to put the Key Word.
2. We will search for this word where on each sheet?
Say something like the value in column B of each row on each sheet

 

[amoverton2]

I did not look at the video.
So give me some specific details

1. What is the name of the Textbox on the userform where you plan to put the Key Word.
2. We will search for this word where on each sheet?
Say something like the value in column B of each row on each sheet

Just use TextBox1 for now, I can change the name later if necessary.
The key word is in column B, starting on row 2 (like your example). Qustion. How hard is it to search the whole sheet(s) by not limiting the search to one column?
Also, how hard is this to do with a multipage userform where I have another search function going on another page in the multipage (or is it better to have its own userform)

 

[My Aswer Is This]

You said:
How hard is it to search the whole sheet(s) by not limiting the search to one column?
It not hard but some people have 300 thousand rows and 4 thousand columns of data.
And you want to search all sheets and some people have 500 sheets.
So to search all those cells on all those sheets may take some time. And some people on the forum think if a script takes 1.5 seconds to run is a long time. Using a script from a Userform is not a problem.

 

[amoverton2]

about 37 columns, 150 rows per sheet, 6 sheets total. 1.5 seconds is super fast!!

 

[My Aswer Is This]

about 37 columns, 150 rows per sheet, 6 sheets total. 1.5 seconds is super fast!!

I'm going to let someone else here on the forum help you. I have never done a search on all cells in all sheets of a workbook and delete the row where value is found. Now if we always searched the same column on every sheet I could help you. I'm sure someone here will have a answer we can both learn from.

 

[amoverton2]

I'm going to let someone else here on the forum help you. I have never done a search on all cells in all sheets of a workbook and delete the row where value is found. Now if we always searched the same column on every sheet I could help you. I'm sure someone here will have a answer we can both learn from.

I can put the keyword in column B (like your example, "Say something like the value in column B of each row on each sheet"), one sheet isn't set up that way but its a quick fix

 

[My Aswer Is This]

You said earlier:
How hard is it to search the whole sheet(s) by not limiting the search to one column?

It's easy to search just column B on all sheets.
But it sounded like you did not want to limit it to one column on each sheet.

 

[amoverton2]

You said earlier:
How hard is it to search the whole sheet(s) by not limiting the search to one column?

It's easy to search just column B on all sheets.
But it sounded like you did not want to limit it to one column on each sheet.

True, I asked how hard it was to search whole sheets vice one column, however for the sake of time here, I can put the data needed to search in column B and search just column B to make it easier. I just have to remember when creating other sheets to make sure column B is my centerpoint.

 

[My Aswer Is This]

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)
    .AutoFilter 1, s
    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