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

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?

--
Rick (MVP - Excel)



"PVANS" <(E-Mail Removed)> wrote in message
news:10017DA8-5373-40BB-875C-(E-Mail Removed)...
> 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

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





"PVANS" <(E-Mail Removed)> wrote in message
news:10017DA8-5373-40BB-875C-(E-Mail Removed)...
> 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

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" wrote:

> 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

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

"Rick Rothstein" wrote:

> 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?
>
> --
> Rick (MVP - Excel)
>
>
>
> "PVANS" <(E-Mail Removed)> wrote in message
> news:10017DA8-5373-40BB-875C-(E-Mail Removed)...
> > 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
>
> .
>

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" wrote:

> 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" wrote:
>
> > 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

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.


"PVANS" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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

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.


"Mike H" wrote:

> 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.
>
>
> "PVANS" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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

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.

--
Rick (MVP - Excel)



"PVANS" <(E-Mail Removed)> wrote in message
news577EAEC-353A-4055-8525-(E-Mail Removed)...
> 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" wrote:
>
>> 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" wrote:
>>
>> > 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