Run time error 40036, similar procedures - works in one sheet not in the other

I have 4 procedures in a workbook to clear worksheets’ rows; 3 of the procedures work, one generates “Run-time error 40036, Application defined or Object defined error”. My only difference in the procedures is the worksheet name. Can someone tell me why procedure “Public Sub ClrComparison()” generates the error? They are in module “modClearSheetRows”.

These three work:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This one generates the run time error

Please Login or Register  to view this content.

The only obvious difference is that the comparison sheet has blank lines in it.

Try deleting the blank lines, for the purpose of the test, and running your macro again. If that works, you need another way of generating the range to be cleared. I'm not a fan of UsedRange as it can be unreliable.

I tend to use Range("A" & Rows.Count).End(xlUp).Row to get the genuine last row
and Cells(Columns.Count, 1).End(xlToLeft).Column to get the last column.

You can then use these to define the range to be cleared.


Regards, TMS

Hi, I'm not sure why, but the reason for the error is that you have declared your own type sKey in the sheet module for sheet Comparison.
If you try to compile your project you will get error that you cannot define Public user-defined type in object module. if you move this to normal module or just comment it, it will work just fine.
Buran

Originally Posted by buran

Hi, I'm not sure why, but the reason for the error is that you have declared your own type sKey in the sheet module for sheet Comparison.
If you try to compile your project you will get error that you cannot define Public user-defined type in object module. if you move this to normal module or just comment it, it will work just fine.
Buran

Note that I refer to following code

Please Login or Register  to view this content.

that is right after Option Explicit statement

I was not able to see anything wrong with your code, so I decided to try really simple thing - I activated the Comparison sheet and tried to execute Debug.Print ActiveSheet.Name in the Immediate Window and I get the same error. Now, knowing that there is something wrong with the worksheet, not your code, I tried to compile the project and get the error mentioned in my reply. So, I commented out the problem code and it worked just fine. It makes sense, because your code was not compiled and it compiles the project on the fly. so it rise an error only when it uses worksheet Comparison and tries to compile the code in the object module for that sheet.
I hope I make myself clear and it helps on how I debug the error.
Buran