delete row off 5 worksheets containing specific value

[PVANS]

Good Afternoon all,

Wonder if someone could give me a suggestion here - I have multiple
worksheets in a workbook. What I would like to do is have the user type in a
value into an input box, and if that value is found either in column A or
column B on Sheet1,Sheet2,Sheet3,Sheet4,Sheet5 (if its on one, it will be on
all 5) for it to delete the entire row.

Can this be done? if so, please could you suggest a method

Thanks so much, I appreciate the assistance.

Regards,

Paul

 

[Rick Rothstein]

If it is on one sheet, will it be on the same row on the other four sheets
or just somewhere else on those other sheets? Can the value be on a single
sheet in more than one row or, once found on a sheet, will that be the only
occurrence of that value on the sheet?

 

[JLGWhiz]

Try this:

Sub deleteRow()
Dim lr As Long, sh As Worksheet, rng As Range
matchThis = InputBox("Enter data to match for row deletion.",
"DATA TO MATCH")
For Each sh In ThisWorkbook.Sheets
lr = sh.Cells.Find(What:="*", After:=sh.Range("A1"), _
LookAt:=xlPart, LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Row
Set rng = sh.Range("A2:B" & lr)
For Each c In rng
If c.Value = matchThis Then
sh.Rows(c.Row).Delete
End If
Next
Next
End Sub

 

[Mike H]

Will it be on the same row in all sheets?
Could there be multiple instances of the value been sought?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[PVANS]

Hi Rick,

It will only appear once on each sheet - but not definitely on the same
numbered row

definitely in either column A or B and definetly on every sheet.

If it would be easier, I am able to change the one sheet slightly so that
the value will definitely be in column B on all 5 sheets

 

[PVANS]

Hi Mike,

There will only be one instance of this value

However, it won't necessariliy be on the same row on all sheets.

I can make it so it is definitely in column B not Column A or B if that
makes it easier

 

[Mike H]

I can make it so it is definitely in column B not Column A or B if that
makes it easier

No that's fine working on it now
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Hi,

Try this. I'm not sure I've used the best compare method. I have assumed the
value being looked for is the only value in the cell and not part of a larger
string

Sub ClearIt()
Dim Ws As Worksheet
Dim ResPonse As String
Dim LastRow As Long
Dim LastrowA As Long
Set sht = Sheets("Sheet1")
S = "Sheet1,Sheet2,Sheet3,Sheet4,Sheet5"
V = Split(S, ",")
ResPonse = UCase(InputBox("Enter value"))
If ResPonse = vbNullString Then Exit Sub
LastRow = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
For x = LastRow To 1 Step -1
If UCase(Cells(x, 1)) = ResPonse Or UCase(Cells(x, 2)) = ResPonse Then
For Each Ws In ThisWorkbook.Worksheets
If Not IsError(Application.Match(Ws.Name, V, 0)) Then
LastrowA = WorksheetFunction.Max(Ws.Cells(Cells.Rows.Count,
"A").End(xlUp).Row, Ws.Cells(Cells.Rows.Count, "B").End(xlUp).Row)

Set MyRange = Ws.Range("A1:A" & LastrowA)
For Each c In MyRange

If UCase(c.Value) = ResPonse Or UCase(c.Offset(, 1).Value) = ResPonse Then
c.EntireRow.Delete
Exit For
End If
Next
End If
Next Ws
End If
Next x
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rick Rothstein]

Being in either Column A or B is not a problem. Give this macro a try...

Sub ClearResponseRows()
Dim X As Long, WS As Worksheet, Response As String, S() As String
Response = UCase(InputBox("Enter value"))
If Response = vbNullString Then Exit Sub
S = Split("Sheet1,Sheet2,Sheet3,Sheet4,Sheet5", ",")
On Error GoTo NotFound
For X = 0 To UBound(S)
Worksheets(S(X)).Range("A:B").Find(What:=Response, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False).EntireRow.Delete
Next
NotFound:
End Sub

Note: If you need to change the sheet names this macro applies to, you would
change the obvious text String argument to the Split function (4th statement
in the macro)... the text is comma delimited with no spaces around the
commas.