VBA Failure to delete!

Big Lar

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

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
Replace the line with:
Code:
Set cName = Sheets("Settings").Range("C130")
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top