Find a value and delete that row

[Guest]

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

 

[Guest]

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

 

[Guest]

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!

 

[NickHK]

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

 

[Guest]

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

 

[Guest]

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!

 

[Guest]

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

 

[Guest]

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