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

 

[MARK858]

What is the value of locations when it fails?

 

[Norie]

Does this work?

With Sheets("TLS Inquiry")
    If locations < 500 Then
        .Rows(4 + (locations)).Resize(500 - locations).Delete
        With Sheets("Locations")
            .Visible = True
            .Rows(4 + (locations)).Resize(500 - locations).Delete
            .Visible = xlVeryHidden
        End With
    
    End If
End With

 

[tbakbradley]

What is the value of locations when it fails?

It can be anything and fail. I've tried 1, 5, 10, 30 just to name a few. Most of us have Windows 7. I just asked a person who just received a new laptop with Windows 10, and it worked fine for him. However, the person who discovered the problem is also on Windows 10. I'd like to see a screen shot to make sure they know for certain.

 

[tbakbradley]

Does this work?

With Sheets("TLS Inquiry")
    If locations < 500 Then
        .Rows(4 + (locations)).Resize(500 - locations).Delete
        With Sheets("Locations")
            .Visible = True
            .Rows(4 + (locations)).Resize(500 - locations).Delete
            .Visible = xlVeryHidden
        End With
    
    End If
End With

Let me try

 

[tbakbradley]

Does this work?

With Sheets("TLS Inquiry")
    If locations < 500 Then
        .Rows(4 + (locations)).Resize(500 - locations).Delete
        With Sheets("Locations")
            .Visible = True
            .Rows(4 + (locations)).Resize(500 - locations).Delete
            .Visible = xlVeryHidden
        End With
    
    End If
End With

Same Error, Same line of code

 

[Norie]

Has anything changed in the workbook you are running the code on?

 

[Martvg]

Hi tbakbradley,

Could you share the rest of your code?

Best regards,
Mart

 

[tbakbradley]

Has anything changed in the workbook you are running the code on?

Not only has nothing changed on that workbook (has been working fine on this version since 2/21/17 with no errors), but the copy on the two different servers and my machine all have the same problem. To make things crazier, all the versions back to January of 2015 are also now producing this Run Time Error. I don't believe it's a problem with the code as it's been working for many years. Actually, I used to have inefficient Code to delete the rows and some folks on this site gave me the New Code for deleting the rows that is so much faster.

Two of the folks on my team have recently been upgraded with a new Laptop using Windows 10 OS and Office 2013. They have no problems at all running the Spreadsheet Macro as of this afternoon when I found out. No Run Time Errors. The only people that are having the problems are those on Windows 7 and Office 2010 that I'm aware of. I'm wondering if there has been an MS update or something that has caused this, as it just happened. A colleague stated he searched through MS's website and found several people complaining that they have spreadsheets that have worked for 10 years, that now are not. I have not been on MS's site yet.

 

[tbakbradley]

Hi tbakbradley,

Could you share the rest of your code?

Best regards,
Mart

Please note that I'm not a programmer nor an expert in any way with VBA. I wear several hats within my organization and build several tools with VBA. I'm sure there are much better ways to do what I do.

Sub Build_Detailed_Form_Button()

   
If Sheets("Cover").Range("CaseDropDown") = "Inquiry" Then
     
    Sheets("TLS Inquiry").Visible = True
   
    Sheets("TLS Inquiry").Select
    Worksheets("TLS Inquiry").Range("B2").Select
        
    'This section builds the number of rows as entered from Sales in the Input Box
    
    locations = InputBox("Enter Total number of Locations for Inquiry Request.")
    Do While locations = "" Or Not CStr(Val(locations)) = locations Or Val(locations) > 499 Or Val(locations) < 1
    MsgBox "You must specify a (Number) between 1 and 499.  If more than 499 locations are required, please alert SNSE.", vbExclamation, "Select a Number Between 1 and 499"
    locations = InputBox("Enter Total number of Locations for Inquiry Request.")
    Loop
    Ans = MsgBox("The number of locations you entered is above, is this correct? Please note that if you enter a number, click Yes and it's not correct, Sales must open a new Request Form Spreadsheet and redo.", vbYesNo, locations)
    Select Case Ans
    Case vbNo
    Do While Ans = vbNo
    locations = InputBox("Enter Total number of Locations for Inquiry Request.")
    Do While locations = "" Or Not CStr(Val(locations)) = locations Or Val(locations) > 499 Or Val(locations) < 1
    MsgBox "You must specify a (Number) between 1 and 499.  If more than 499 locations are required, please alert SNSE.", vbExclamation, "Select a Number Between 1 and 499"
    locations = InputBox("Enter Total number of Locations for Inquiry Request.")
    Loop
    Ans = MsgBox("The number of locations you entered is above, is this correct? Please note that if you enter a number, click Yes and it's not correct, Sales must open a new Request Form Spreadsheet and redo.", vbYesNo, locations)
    Loop
    Case vbYes
    End Select
    'Placing Number of Locations on Network Tab and Cover Tab
    Sheets("Cover").Select
    Sheets("Cover").Range("B67") = "Number of Locations specified by Sales:"
    Sheets("Cover").Range("G67") = locations
    Sheets("Network").Visible = True
    Sheets("Network").Range("K10").Value = locations
    Sheets("Network").Visible = xlVeryHidden
    
    'Deleting Rows for TLS Inquiry Form (500-Locations Specified)
    Sheets("TLS Inquiry").Select
    If locations < 500 Then
    Rows(4 + (locations)).Resize(500 - locations).Delete
    Sheets("Locations").Visible = True
    Sheets("Locations").Select
    Rows(4 + (locations)).Resize(500 - locations).Delete
    Range("B4").Select
    Sheets("Locations").Visible = xlVeryHidden
    Sheets("TLS Inquiry").Select
    End If
 
 Sheets("Cover").Select
 ActiveSheet.Shapes("Build Button").Visible = False
 Sheets("TLS Inquiry").Select
 Range("B4").Select
 
 End If
End Sub