Delete method of range class failed on previously working code

[GPR]

I have a workbook that has been used by me and several co-workers that contains several delete loops. It's the strangest thing, we've been using it with no issues for close to a year now with nothing other than some formatting changes. Last changes of any type were done in November. Now, out of nowhere, I'm getting an error in the code during one of the delete loops. delete method of range class failed.

Some things I've looked into and tried. There are no filters on the sheets. No other formula's changed. I've tried to copy the sheets (both by copying entire sheets and copying cells and pasting onto new sheets) and the code over to a new workbook. Even had the IT department recover the document from when it was still working with no errors. Nothing works. Other people have done it on their machines, same error. I even went back to older outdated versions to try (that haven't been touched in 6 months) and now suddenly get the same error. I stop the code before this loop to try to delete one of the columns manually, and when I do, excel immediately crashes. No error messages, just "Excel has stopped working" and it closes and tries to recover (but never can).

I can't for the life of me figure this out.

Here is the code that errors out now... but never did before.

Sub deleteColumnDept()




Dim empColumn1 As Range
Dim empCell1 As Range
Dim shtName1 As String



Set empColumn1 = Worksheets("DeptRef").Range("A2", Worksheets("DeptRef").Range("A" & Rows.Count).End(xlUp))




    For Each empCell1 In empColumn1
        If empCell1.Value <> "" Then
            
            shtName1 = empCell1.Value
            
             
            
                For i = 200 To 5 Step -1
                    If Sheets(shtName1).Cells(1, i).Value = "X" Then
                        Sheets(shtName1).Cells(1, i).EntireColumn.Delete  '<---ERROR HERE
                    End If
                Next i
        End If
        
    Next empCell1
                
    


End Sub

And this sub is called right before the one above that errors. It's basically identical. Just runs on a different set of sheets. And runs with no issues.

Sub deleteColumnEmp()




Dim empColumn As Range
Dim empCell As Range
Dim shtName As String





Set empColumn = Worksheets("EmployeeRef").Range("B2", Worksheets("EmployeeRef").Range("B" & Rows.Count).End(xlUp))




    For Each empCell In empColumn
        If empCell.Value <> "" Then
            
            shtName = empCell.Value
            
                       
            
                For i = 200 To 5 Step -1
                    If Sheets(shtName).Cells(1, i).Value = "X" Then
                        Sheets(shtName).Cells(1, i).EntireColumn.Delete
                    End If
                Next i
        End If
        
    Next empCell
                
End Sub

 

[JoeMo]

If you change EntireColumn.Delete in the errant line to EntireColumn.ClearContents, does it run and clear the contents?

 

[GPR]

If you change EntireColumn.Delete in the errant line to EntireColumn.ClearContents, does it run and clear the contents?

I can try it, but I need the data to shift.

 

[GPR]

OK. Tried clear contents and got an error about being disconnected with source at the same spot.

I have been working with this for a while, I don't think it's the code itself. It's something within the workbook. I switched the order of the two sets of code. It always errors on whichever I put second with the first running fine. If I stop before the loop and delete manually, it crashes. If I try to save a copy the file prior to trying to delete manually, it won't let me save because the file contains errors. I'm at a loss.

 

[JoeMo]

OK. Tried clear contents and got an error about being disconnected with source at the same spot.

I have been working with this for a while, I don't think it's the code itself. It's something within the workbook. I switched the order of the two sets of code. It always errors on whichever I put second with the first running fine. If I stop before the loop and delete manually, it crashes. If I try to save a copy the file prior to trying to delete manually, it won't let me save because the file contains errors. I'm at a loss.

Agree.