Code worked for 5 years, not it's not- Deleting Rows

[tbakbradley]

Hello all,

I built a tool for my organization several years ago. There have been many versions within that time. Our company has received several Updates to our Computers, where we have had to reboot multiple times a day. One of our Sales Engineers let me know they could not Build their Request from the Tool I built. I checked, and sure enough, I'm getting a Run-Time Error 2147417848 (80010108)- Method 'Delete' of object 'Range' failed. This file is saved on two different servers as well as my Hard Drive. All produce the same Run Time Error. My entire team has tried from one of the Servers to receive the same Run Time error as me and the Sales Engineers.

The Code below has not changed for about 5 years or so. I've had SEVERAL Versions of the overall tool in the last five years. What is odd, is that I've went back and ran ever Version back to 2015, and ALL of them produce the same Run Time Error as of today. I'm wondering if one of the updates the Company has pushed to all of our Laptops has created some Registry issue with Excel and VBA with some lines of Code?

When the user clicks the Macro Button, they receive an Input Box asking the User how many Locations they require. Once they enter the number, it is stored into the Variable "locations". There are 500 Locations prebuilt on both the "TLS Inquiry" and the "Locations" Worksheets. The header is on the 3rd Row and the Data starts on the 4th, so you understand the code. The Macro continues to work fine for the "TLS Inquiry" Worksheet, but fails at the "Locations" Worksheet portion of the code. It actually deletes all the appropriate rows, but then gives the Run Time error I provided AND the entire spreadsheet locks up and any Totals now show "#REF".

Any idea what could be going on? I've been banging my head on my desk all day as now all Spreadsheets back to 2015 (didn't check further back) no longer work and we've not had a problem until Friday. Also, although I do all my work in Excel 2010, I have to save the Excel File as 2003 because our massive System Screen Scrapes data from the Spreadsheet and the system doesn't support .xlsm. Don't ask!!

The

Rows(4 + (locations)).Resize(500 - locations).Delete

works for the "TLS Inquiry" Worksheet but the Run Time Error happens when it's run again on the "Locations" Worksheet.

Sheets("TLS Inquiry").Select
    If locations < 500 Then
    Rows(4 + (locations)).Resize(500 - locations).Delete
    Sheets("Locations").Visible = True
    Sheets("Locations").Select
   [COLOR=#ff0000] Rows(4 + (locations)).Resize(500 - locations).Delete[/COLOR]
    Range("B4").Select
    Sheets("Locations").Visible = xlVeryHidden
    Sheets("TLS Inquiry").Select
    End If

 

[tbakbradley]

After several attempts to try different things and try to track down what was sent to our laptops via a company update, I decided to comment out the section of the code now producing the Run Time Error. I changed the process of that Worksheet a bit so that the User still receives all the correct data, but in a slightly different format than before. Once most of us have moved to Office 2013 (which doesn't receive the Run Time Error), I'll turn the code back on.

 

[SlinkyR]

After several attempts to try different things and try to track down what was sent to our laptops via a company update, I decided to comment out the section of the code now producing the Run Time Error. I changed the process of that Worksheet a bit so that the User still receives all the correct data, but in a slightly different format than before. Once most of us have moved to Office 2013 (which doesn't receive the Run Time Error), I'll turn the code back on.

I had the same issue occur, there was a Microsoft update that was release on 3/28/17 and my company installed them on 3/30. This caused a number of functioning macros to break with the same error code. Microsoft released an update to the fix on about 4/4 which corrects the issue. You'll need to reach out to your IT department, but they should be able to install it in about 5 minutes. It worked for me and all of our macros now run again.