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