VBA Failure to delete!

Big Lar

Well-known Member
Joined
May 19, 2002
Messages
552
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!


Code:
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,211
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:
Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
9,562
Try this macro. Insert the missing sheet names where indicated.
Code:
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
 
Last edited:

Big Lar

Well-known Member
Joined
May 19, 2002
Messages
552
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.



Rich (BB code):
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

use

Code:
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
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,562
Replace the line with:
Code:
Set cName = Sheets("Settings").Range("C130")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,048
Messages
5,526,472
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top