Excel crashes after numerous range updates with run-time error 1004

[whitehawk81]

Hi there!
I'm currently getting run-time error 1004: "Unable to get the CurrentRegion property of the Range class" within following subroutine:

Sub clearData()
Dim modDataRng As Range

Set modDataRng = Sheet2.Range("B4:B7")

If WorksheetFunction.CountA(modDataRng) > 0 Then
    modDataRng.ClearContents
End If

If WorksheetFunction.CountA(Sheet3.Range("A:A")) > 1 Then
    Sheet3.Range("A2").CurrentRegion.Offset(1).ClearContents   'I get the error at this line
End If

End Sub

The strange thing is, I only get this error after I selected a few entries in the upper userform listbox.
This issue started, when I changed the ranges to tables on the "Servers" sheet.
I also uploaded an example file to demonstrate the issue better.
You can open the userform with the "Manage Plan" button on "Planlist" sheet.
I suspect, that it has to do something with the tablerange updates.
Do you have an idea, what could cause this issue?

 

[Joe4]

I do not have the ability to download/view your file from my current location, but if you put this line of code above the one returning the error, can you let us know what it returns when you run the code?

MsgBox Sheet3.Range("A2").CurrentRegion.Address

 

[whitehawk81]

I do not have the ability to download/view your file from my current location, but if you put this line of code above the one returning the error, can you let us know what it returns when you run the code?

MsgBox Sheet3.Range("A2").CurrentRegion.Address

I get the range address: $A$1:$F$12, which is correct.

 

[Joe4]

OK, now what if you add the "Offset" piece to it, i.e.

MsgBox Sheet3.Range("A2").CurrentRegion.Offset(1).Address

Does that still return a value without errors?
Do you have any merged cells or locked cells in that range that would prevent the values from being cleared?

 

[whitehawk81]

OK, now what if you add the "Offset" piece to it, i.e.

MsgBox Sheet3.Range("A2").CurrentRegion.Offset(1).Address

Does that still return a value without errors?
Do you have any merged cells or locked cells in that range that would prevent the values from being cleared?

I also get the correct range $A$2:$F$13 with the offset funcion.
There are no locked or merged cells, no formulas or formatting applied within the range.

 

[Joe4]

Hmmm... I am not sure then.
Later on tonight, I will have access to my home computer, and I will see if I can download your file then, and take a look to see if I can see what is going on.

Just for kicks, try this one last thing. Add in this line of code (in red) and see if it makes any difference.

If WorksheetFunction.CountA(Sheet3.Range("A:A")) > 1 Then
    Sheet3.Activate
    Sheet3.Range("A2").CurrentRegion.Offset(1).ClearContents   'I get the error at this line
End If

By the way, Sheet3 isn't hidden, is it?

 

[whitehawk81]

Hmmm... I am not sure then.
Later on tonight, I will have access to my home computer, and I will see if I can download your file then, and take a look to see if I can see what is going on.

Just for kicks, try this one last thing. Add in this line of code (in red) and see if it makes any difference.

If WorksheetFunction.CountA(Sheet3.Range("A:A")) > 1 Then
    Sheet3.Activate
    Sheet3.Range("A2").CurrentRegion.Offset(1).ClearContents   'I get the error at this line
End If

By the way, Sheet3 isn't hidden, is it?

Thanks! I put the range address into a variable and added it to the watchlist. When the error occurs, the address stays empty. But then Excel crashes completely.
It's like the macro would get into an infinite loop.

 

[Joe4]

Thanks! I put the range address into a variable and added it to the watchlist. When the error occurs, the address stays empty. But then Excel crashes completely.
It's like the macro would get into an infinite loop.

OK, that is good to know. Then I think you should be able to use an IF...THEN statement to check to see if the address is empty, and if it is, then skip that step (and only run it if it is not empty).

 

[whitehawk81]

OK, that is good to know. Then I think you should be able to use an IF...THEN statement to check to see if the address is empty, and if it is, then skip that step (and only run it if it is not empty).

It was a nice idea. I don't get the error with the range address now, but Excel still crashes.

 

[Joe4]

Is this the only code you are running when it crashes, or is there any automated code which may also be running?