VBA Failure to delete!

[Big Lar]

What’s wrong with my code?

Each cName is found on each sheet and the Delete procedure is performed. (as witnessed during debugging)
However, only Sheets(“Net”) and (“ADJ”) Ranges actually get deleted.
Identical code for any sheet between (“Net”) and (“ADJ”) the Range remains.

During debugging, if I manually select the sheets before each procedure is performed, the Range gets deleted. Adding Sheet.Select to the procedure solves the problem…But, why do the first and last procedures work without Sheet.Select?

Truly puzzled!

Set ws = Worksheets("Net")
With Sheets("Net")
Sheets("Net").Unprotect
For i = 3 To 100
    cName = Sheets("Settings").[C130].Value
     If Range("A" & i).Value = cName Then
      ThisWorkbook.Worksheets("Net”).Range("A" & i).Resize(1, 27).Delete Shift:=xlUp
End If
 Next i
  End With
 
Set ws = Worksheets("G1")
With Sheets("G1")
Sheets("G1").Unprotect
For i = 3 To 100
  cName = Sheets("Settings").[C130].Value
    If Range("A" & i).Value = cName Then
     ThisWorkbook.Worksheets("G1").Range("A" & i).Resize(1, 26).Delete Shift:=xlUp
End If
 Next i
  End With
 
Set ws = Worksheets("G2")
With Sheets("G2")
Sheets("G2").Unprotect
For i = 3 To 100
 cName = Sheets("Settings").[C130].Value
      If Range("A" & i).Value = cName Then
       ThisWorkbook.Worksheets("G2").Range("A" & i).Resize(1, 26).Delete Shift:=xlUp
End If
 Next i
  End With

''''''''''''''
'eight more sheets with identical code are not shown because.redundant
''''''''''''''  
 
Set ws = Worksheets("ADJ")
With Sheets("ADJ")
Sheets("ADJ").Unprotect
For i = 3 To 100
 cName = UFPS.ComboBox1.Value
    If Range("A" & i).Value = cName Then
     ThisWorkbook.Worksheets("ADJ").Range("A" & i).Resize(1, 24).Delete Shift:=xlUp
End If
 Next i
  End With

 

[JoeMo]

I haven't looked closely at all your code, but I'd guess you are not qualifying the sheets correctly within your With-End With blocks. Try changing those blocks as in this example:

With Sheets("Net")
    .Unprotect
    For i = 3 To 100
        cName = Sheets("Settings").[C130].Value
        If .Range("A" & i).Value = cName Then
            .Range("A" & i).Resize(1, 27).Delete Shift:=xlUp
        End If
    Next i
End With

 

[mumps]

Try this macro. Insert the missing sheet names where indicated.

Sub BigLar()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, cName As Range, i As Long
    cName = Sheets("Settings").Range("C130")
    For Each ws In Sheets(Array("Net", "G1", "G2", "ADJ")) 'insert the additional sheet names in the array
        ws.Unprotect
        For i = 100 To 3 Step -1
            If ws.Range("A" & i).Value = cName.Value Then
                ws.Range("A" & i).Resize(1, 27).Delete Shift:=xlUp
            End If
        Next i
        ws.Protect
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[Big Lar]

Mumps,
Thanks for your suggestion. I like it.

I’ve adapted it to my procedure with these modifications:
Dim ws As Worksheet and I As Long are removed because they’re duplicated
I’m repeating the code due to Resize differences on my sets of worksheets.

Now, I am stuck again and cannot resolve (due to my lack of understanding)
Run-Time error ‘91’:
Object variable or With block variable not set at:
cName = Sheets("Settings").Range("C130")

Additional advice is appreciated.

Application.ScreenUpdating = False
    Dim cName As Range
    cName = Sheets("Settings").Range("C130")
    For Each ws In Sheets(Array("Net", "N1", "N2", "N3", "N4", "ADJ"))
        ws.Unprotect
        For i = 100 To 3 Step -1
            If ws.Range("A" & i).Value = cName.Value Then
                ws.Range("A" & i).Resize(1, 27).Delete Shift:=xlUp
            End If
        Next i
        ws.Protect
    Next ws
 
    For Each ws In Sheets(Array("G1", "G2", "G3", "G4"))
        ws.Unprotect
        For i = 100 To 3 Step -1
            If ws.Range("A" & i).Value = cName.Value Then
                ws.Range("A" & i).Resize(1, 26).Delete Shift:=xlUp
            End If
        Next i
        ws.Protect
    Next ws
    Application.ScreenUpdating = True

 

[DanteAmor]

use

Application.ScreenUpdating = False
    Dim cName As [COLOR=#0000ff]Variant[/COLOR]


    cName = Sheets("Settings").Range("C130")
    For Each ws In Sheets(Array("Net", "N1", "N2", "N3", "N4", "ADJ"))
        ws.Unprotect
        For i = 100 To 3 Step -1
            If ws.Range("A" & i).Value = [COLOR=#0000ff]cName [/COLOR]Then
                ws.Range("A" & i).Resize(1, 27).Delete Shift:=xlUp
            End If
        Next i
        ws.Protect
    Next ws
 
    For Each ws In Sheets(Array("G1", "G2", "G3", "G4"))
        ws.Unprotect
        For i = 100 To 3 Step -1
            If ws.Range("A" & i).Value = [COLOR=#0000ff]cName [/COLOR]Then
                ws.Range("A" & i).Resize(1, 26).Delete Shift:=xlUp
            End If
        Next i
        ws.Protect
    Next ws
    Application.ScreenUpdating = True

 

[mumps]

Replace the line with:

Set cName = Sheets("Settings").Range("C130")

 

[Big Lar]

Thanks for the solution...
it works perfectly!

 

[mumps]

My pleasure.