Find a value and delete that row
 

Hi all,
Can someone help me out with some code that will look at the value in B3 on
a sheet called "spc", then look for that value on sheets "syr", "tar", "grn"
etc...and delete that entire row.

As always...appreciate the help!

Tom

Find a value and delete that row
 

Where on "syr", "tar", "grn" etc... are we to look for the value in B3
because it would take a very long time so look in all cells. Is it a range or
a particular cell?

Mike

"Tom" wrote:

Hi all,
Can someone help me out with some code that will look at the value in B3 on
a sheet called "spc", then look for that value on sheets "syr", "tar", "grn"
etc...and delete that entire row.

As always...appreciate the help!

Tom

Find a value and delete that row
 

I realized after I posted this that I should have specified...sorry about that!

For the most part, it should look in column A on those sheets. There are a
couple sheets that has it in column B but I could change them ALL to column A
if it isn't possible to designate which column on which sheets to look for
the value.

Thanks!

"Mike H" wrote:

Where on "syr", "tar", "grn" etc... are we to look for the value in B3
because it would take a very long time so look in all cells. Is it a range or
a particular cell?

Mike

"Tom" wrote:

Hi all,
Can someone help me out with some code that will look at the value in B3 on
a sheet called "spc", then look for that value on sheets "syr", "tar", "grn"
etc...and delete that entire row.

As always...appreciate the help!

Tom

Find a value and delete that row
 

Something like this ?

Private Sub CommandButton2_Click()
Dim WS As Worksheet
Dim FoundCell As Range
Dim SearchVal As Variant

SearchVal = Worksheets(1).Range("B3").Value

For Each WS In Worksheets(Array(2, 3)) 'Change to the sheet names
With WS.Cells 'Change to the range required
Set FoundCell = .Find(SearchVal)
Do Until FoundCell Is Nothing
FoundCell.EntireRow.Delete
Set FoundCell = .FindNext
Loop
End With
Next

End Sub

NickHK

"Tom" wrote in message
...
Hi all,
Can someone help me out with some code that will look at the value in B3
on
a sheet called "spc", then look for that value on sheets "syr", "tar",
"grn"
etc...and delete that entire row.

As always...appreciate the help!

Tom

Find a value and delete that row
 

Tom,

It would still take a very long time to search the entire column A & B
particularly as I don't know what etc means (are there 1000 sheets). That
said, try this current set to search very sheet except "spc" (case sensitive)
for the value in B3

Sub stantialsearch()
myValue = Worksheets("spc").Range("B3").Value
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Select
If wSheet.Name = ("spc") Then GoTo 100
Set myrange = ActiveSheet.Range("A1:B100") '< Alter to suit
For Each c In myrange
c.Select
If c.Value = myValue Then Selection.EntireRow.Delete
Next c
100
Next wSheet
End Sub

Mike

"Tom" wrote:

I realized after I posted this that I should have specified...sorry about that!

For the most part, it should look in column A on those sheets. There are a
couple sheets that has it in column B but I could change them ALL to column A
if it isn't possible to designate which column on which sheets to look for
the value.

Thanks!

"Mike H" wrote:

Where on "syr", "tar", "grn" etc... are we to look for the value in B3
because it would take a very long time so look in all cells. Is it a range or
a particular cell?

Mike

"Tom" wrote:

Hi all,
Can someone help me out with some code that will look at the value in B3 on
a sheet called "spc", then look for that value on sheets "syr", "tar", "grn"
etc...and delete that entire row.

As always...appreciate the help!

Tom

Find a value and delete that row
 

Mike,
There are actually 12 sheets that I need to search for the value. Each of
those sheets has approx 75 rows that has data. The "etc" that I stated refers
to those 12 sheets (I just didn't name them all except for the examples I
gave). I would like to search ONLY the specified sheets - that is, not every
sheet in the workbook since I still need to keep SOME of that data on certain
sheets. (There are about 100 sheets in the workbook).

Very much appreciate the help!

"Mike H" wrote:

Tom,

It would still take a very long time to search the entire column A & B
particularly as I don't know what etc means (are there 1000 sheets). That
said, try this current set to search very sheet except "spc" (case sensitive)
for the value in B3

Sub stantialsearch()
myValue = Worksheets("spc").Range("B3").Value
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Select
If wSheet.Name = ("spc") Then GoTo 100
Set myrange = ActiveSheet.Range("A1:B100") '< Alter to suit
For Each c In myrange
c.Select
If c.Value = myValue Then Selection.EntireRow.Delete
Next c
100
Next wSheet
End Sub

Mike

"Tom" wrote:

I realized after I posted this that I should have specified...sorry about that!

For the most part, it should look in column A on those sheets. There are a
couple sheets that has it in column B but I could change them ALL to column A
if it isn't possible to designate which column on which sheets to look for
the value.

Thanks!

"Mike H" wrote:

Where on "syr", "tar", "grn" etc... are we to look for the value in B3
because it would take a very long time so look in all cells. Is it a range or
a particular cell?

Mike

"Tom" wrote:

Hi all,
Can someone help me out with some code that will look at the value in B3 on
a sheet called "spc", then look for that value on sheets "syr", "tar", "grn"
etc...and delete that entire row.

As always...appreciate the help!

Tom

Find a value and delete that row
 

Tom,

Create a list of the sheet you want to search. In this case they are in
column A starting in A1 then use this code:-

Sub stantialsearch()
myValue = Worksheets("spc").Range("B3").Value
Dim wSheet As Worksheet
x = 1
Do Until Name = ""
Name = Worksheets("spc").Cells(x, 1).Value
Worksheets(Name).Select
Set myrange = ActiveSheet.Range("A1:B75") '< Alter to suit
For Each c In myrange
c.Select
If c.Value = myValue Then
Selection.EntireRow.Delete
End If
Next c
100
x = x + 1
Loop
End Sub


Mike

"Tom" wrote:

Mike,
There are actually 12 sheets that I need to search for the value. Each of
those sheets has approx 75 rows that has data. The "etc" that I stated refers
to those 12 sheets (I just didn't name them all except for the examples I
gave). I would like to search ONLY the specified sheets - that is, not every
sheet in the workbook since I still need to keep SOME of that data on certain
sheets. (There are about 100 sheets in the workbook).

Very much appreciate the help!

"Mike H" wrote:

Tom,

It would still take a very long time to search the entire column A & B
particularly as I don't know what etc means (are there 1000 sheets). That
said, try this current set to search very sheet except "spc" (case sensitive)
for the value in B3

Sub stantialsearch()
myValue = Worksheets("spc").Range("B3").Value
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Select
If wSheet.Name = ("spc") Then GoTo 100
Set myrange = ActiveSheet.Range("A1:B100") '< Alter to suit
For Each c In myrange
c.Select
If c.Value = myValue Then Selection.EntireRow.Delete
Next c
100
Next wSheet
End Sub

Mike

"Tom" wrote:

I realized after I posted this that I should have specified...sorry about that!

For the most part, it should look in column A on those sheets. There are a
couple sheets that has it in column B but I could change them ALL to column A
if it isn't possible to designate which column on which sheets to look for
the value.

Thanks!

"Mike H" wrote:

Where on "syr", "tar", "grn" etc... are we to look for the value in B3
because it would take a very long time so look in all cells. Is it a range or
a particular cell?

Mike

"Tom" wrote:

Hi all,
Can someone help me out with some code that will look at the value in B3 on
a sheet called "spc", then look for that value on sheets "syr", "tar", "grn"
etc...and delete that entire row.

As always...appreciate the help!

Tom

Find a value and delete that row
 

Mike,
Thanks for all your efforts...very much appreciated!

"Mike H" wrote:

Tom,

Create a list of the sheet you want to search. In this case they are in
column A starting in A1 then use this code:-

Sub stantialsearch()
myValue = Worksheets("spc").Range("B3").Value
Dim wSheet As Worksheet
x = 1
Do Until Name = ""
Name = Worksheets("spc").Cells(x, 1).Value
Worksheets(Name).Select
Set myrange = ActiveSheet.Range("A1:B75") '< Alter to suit
For Each c In myrange
c.Select
If c.Value = myValue Then
Selection.EntireRow.Delete
End If
Next c
100
x = x + 1
Loop
End Sub


Mike

"Tom" wrote:

Mike,
There are actually 12 sheets that I need to search for the value. Each of
those sheets has approx 75 rows that has data. The "etc" that I stated refers
to those 12 sheets (I just didn't name them all except for the examples I
gave). I would like to search ONLY the specified sheets - that is, not every
sheet in the workbook since I still need to keep SOME of that data on certain
sheets. (There are about 100 sheets in the workbook).

Very much appreciate the help!

"Mike H" wrote:

Tom,

It would still take a very long time to search the entire column A & B
particularly as I don't know what etc means (are there 1000 sheets). That
said, try this current set to search very sheet except "spc" (case sensitive)
for the value in B3

Sub stantialsearch()
myValue = Worksheets("spc").Range("B3").Value
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Select
If wSheet.Name = ("spc") Then GoTo 100
Set myrange = ActiveSheet.Range("A1:B100") '< Alter to suit
For Each c In myrange
c.Select
If c.Value = myValue Then Selection.EntireRow.Delete
Next c
100
Next wSheet
End Sub

Mike

"Tom" wrote:

I realized after I posted this that I should have specified...sorry about that!

For the most part, it should look in column A on those sheets. There are a
couple sheets that has it in column B but I could change them ALL to column A
if it isn't possible to designate which column on which sheets to look for
the value.

Thanks!

"Mike H" wrote:

Where on "syr", "tar", "grn" etc... are we to look for the value in B3
because it would take a very long time so look in all cells. Is it a range or
a particular cell?

Mike

"Tom" wrote:

Hi all,
Can someone help me out with some code that will look at the value in B3 on
a sheet called "spc", then look for that value on sheets "syr", "tar", "grn"
etc...and delete that entire row.

As always...appreciate the help!

Tom