Macro to find a cell value, across multiple sheets and then delete the row containing the value

[LauraC1984]

Hi,
I am currently working on a macro that will take the value from cell B12 on sheet name 'Input', It searches many sheets and when the value is found it deletes the row. I have the following macro but it keeps debugging at different points. and I don't understand the errors
can anyone help?

Sub updateJobdelete()
Dim fn As Range, i As Long, sh As Worksheet, adr As String, cnt As Long
Set sh = Sheets("Input")
chg = True
Dim ssh As Worksheet
For Each ssh In ThisWorkbook.Sheets
If ssh.Name <> "Input" And ssh.Name <> "Calendar" And _
ssh.Name <> "List" And ssh.Name <> "2020" Then
ssh.Visible = True
Set fn = ssh.Range("C:C").Find(sh.Range("B12").Value, , xlValues, xlWhole)
If Not fn Is Nothing Then
adr = fn.Address
Do
For i = ("B12") To 1 Step -1
If Range("C" & i).Value = Target.Value Then
Rows(i).delete
ct = ct + 1
End If
If fn Is Nothing Then
cnt = cnt + 1
End If
ssh.Visible = False
Next sheets("Calendar").Select
ActiveWorkbook.Save
If cnt = i - 2 Then
MsgBox "Job Number for job delete not found", vbExclamation, "OOPS"
Else
MsgBox "Change processed successfully", vbInformation, "DONE"
End If
End Sub

 

[steve the fish]

Im afraid i cant see how that code works. I can see a few problems. Explain what you are trying to do. You have a value in a cell and you want to check other sheets for that value? Could the value be a date? They can cause issues. Will/could the value appear more than once in a sheet?

 

[LauraC1984]

Hi sorry, the code is a mix of a few macros I have, I was trying to do it myself but it hasn't worked. so maybe best to start from scratch.
ok I want the macro to search for a value that will be in cell B12, It needs to ignore a few sheets which are noted at the beginning of the macro, I think its this section
For Each ssh In ThisWorkbook.Sheets
If ssh.Name <> "Input" And ssh.Name <> "Calendar" And _
ssh.Name <> "List" And ssh.Name <> "2020" Then
ssh.Visible = True

The value can be found in these sheets but I don't want the macro to do anything with them.

the value shouldn't be found more than once in any other sheets, and should be found in column C of the worksheet.
Once the value is found, I want it to delete the row in which the value is found.
Then I want it to go back to the original input screen and delete the original values
all sheets that are to be searched are hidden and need to stay hidden

does this help?

 

[Fluff]

What sort of value is in B12?
Also, when you say

Then I want it to go back to the original input screen and delete the original values

Do you mean to delete the contents of B12?

 

[steve the fish]

See if something like this does the deletions as required.

Dim myFind As String, myVal As Range, shArr

myFind = Sheets("Input").Range("B12").Value
shArr = Array("Input", "Calendar", "List", "2020")

For Each sh In ThisWorkbook.Worksheets
    If Not IsNumeric(Application.Match(sh.Name, shArr, 0)) Then
        Set myVal = sh.Range("C:C").Find(myFind, , xlValues, xlWhole)
        If Not myVal Is Nothing Then
            sh.Rows(myVal.Row).Delete Shift:=xlUp
        End If
    End If
Next

 

[LauraC1984]

What sort of value is in B12?
Also, when you say

Do you mean to delete the contents of B12?

Hi, its just a 6 digit number
yes to go back and delete the content of B12

 

[LauraC1984]

See if something like this does the deletions as required.

Dim myFind As String, myVal As Range, shArr

myFind = Sheets("Input").Range("B12").Value
shArr = Array("Input", "Calendar", "List", "2020")

For Each sh In ThisWorkbook.Worksheets
    If Not IsNumeric(Application.Match(sh.Name, shArr, 0)) Then
        Set myVal = sh.Range("C:C").Find(myFind, , xlValues, xlWhole)
        If Not myVal Is Nothing Then
            sh.Rows(myVal.Row).Delete Shift:=xlUp
        End If
    End If
Next

thanks Steve sorry still new to all this - do I put my find value between the two , , next to my find?

 

[Fluff]

How about

Sub LauraC()
   Dim Ws As Worksheet
   Dim Fnd As Range
   Dim Crit As Double
   
   Crit = Sheets("Input").Range("B12").Value
   For Each Ws In Worksheets
      Select Case Ws.Name
         Case "Input", "Calendar", "List", "2020"
         Case Else
         Set Fnd = Ws.Range("C:C").Find(Crit, , xlValues, xlWhole, , , False, , False)
         If Not Fnd Is Nothing Then Fnd.EntireRow.Delete
      End Select
   Next Ws
   Sheets("Input").Range("B12").ClearContents
End Sub

 

[LauraC1984]

worked perfect thanks. just got to add the messages to it

 

[Fluff]

You're welcome & thanks for the feedback