how to loop through sheets, from (sheet.name) to ( sheet.name) and find(TextBox1.Value) , then Delete Entire Row that contain the (TextBox1.Value)

• the from (sheet.name) = TextBox2.Value
• the to ( sheet.name) = Textbox3.Value

I am not very good with coding but I try . so any help will be appreciated i do searching and found nothing can help me

and here is my last try of cod

Private Sub cmdega_Click()
Dim shArr, j As Long
Dim ws As Worksheet
Dim fromsheet As String
Dim tosheet As String
Dim id As String

fromsheet = Textbox2.Value
tosheet = TextBox3.Value
id = TextBox1.Value

shArr = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31")



For j = LBound(Array("fromsheet")) To UBound(Array("tosheet"))

    With Sheets(shArr(j))
         .Cells.Find(What:=id, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
              xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
            , SearchFormat:=False).Activate
       .Rows.EntireRow.Select
       .Selection.Delete Shift:=xlUp
    End With
    
Next j

End Sub

CodePudding user response：

Use Select Case

Option Explicit

Private Sub cmdega_Click()
    
    Dim ws As Worksheet, rngFound As Range, rngDelete As Range
    Dim fromsheet As String, tosheet As String
    Dim id As String, firstAddr As String, n As Long
    
    fromsheet = Textbox2.Value
    tosheet = TextBox3.Value
    id = TextBox1.Value
    
    ' check valid input
    If Not (IsNumeric(fromsheet) And IsNumeric(tosheet)) Then
        MsgBox "To or From not numeric", vbExclamation
        Exit Sub
    End If
    
    
    ' loop through sheets
    For Each ws In ThisWorkbook.Sheets
        Set rngDelete = Nothing
       
        Select Case ws.Name
            Case fromsheet To tosheet ' range from to
             
                ' search
                Set rngFound = ws.Range("A:A").Find(What:=id, After:=ws.Range("A1"), _
                LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=True, SearchFormat:=False)

                ' was a row found
                If rngFound Is Nothing Then
                    ' nothing found
                Else
                    firstAddr = rngFound.Address
                    Do
                        
                        ' Debug.Print ws.Name, ws.CodeName, rngFound.Address
                        ' build delete range
                        If rngDelete Is Nothing Then
                            Set rngDelete = rngFound
                        Else
                            Set rngDelete = Application.Union(rngFound, rngDelete)
                        End If
                        n = n   1
                        Set rngFound = ws.Range("A:A").FindNext(rngFound)
                    Loop While rngFound.Address <> firstAddr ' continue search
                    
                End If
        End Select
        
        ' delete rows in one operation
        If Not rngDelete Is Nothing Then
            With rngDelete
                '.EntireRow.Interior.Color = vbRed
                .EntireRow.Delete Shift:=xlUp
            End With
        End If
    Next
      
    MsgBox n & " rows matching '" & id & "' deleted", vbInformation
    
End Sub

CodePudding user response：

Mr. CDP1802 ... i don't know how to thank you .. your code work like magic and like what i need ... thanks thanks thanks and "thanks" to you in every language .

:******************