Runtime error 91 on range variable

I have the following code within a fairly lengthy (I think) module.

As opposed to typing out 'IF mmmYY.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).count' for each IF statement, I have created a range variable called rC but I am getting a Runtime error 91 and I'm not sure why?

I'm guessing it may have something to do with the fact the mmmYY worksheet variable hasn't been set before rC has been set however the code doesn't run unless I set the mmmYY variable within the loop and it also doesn't run if I set the rC within the loop either.

Please Login or Register  to view this content.

Thanks in advance.

Thanks for the reply CK76. I've set mmmYY within the loop. If I set it outside the loop before rC is set, I get the same error message.

Try something like below then. I'm not 100% on AutoFilter.Range method.

Please Login or Register  to view this content.

You may need to adjust .Cells(1) to where your data range starts.

No joy with that either. That's not pasting in anything in to the closedTools tab. I changed to .cells(3) as row 3 is the header row?

Looking at your code more closely. You are changing visible range within loop (by applying Autofilter condition). That defeats the purpose of setting "rC" outside of the loop.

I'd suggest you upload small sample workbook with enough data to demonstrate your need, with manually created output as well.

I think you are better off rethinking code logic.

I spotted another error in the code which I have now corrected. The full code is shown below and is running fine.

I'm just trying to understand the correct way to declare a variable to prevent me from having to show the full lines of code in bold.

Please Login or Register  to view this content.

I don't think you can avoid it, with current code structure/logic. But you really should use With... End With to avoid repeat typing of "mmmYY". Actually, you can do away with "mmmYY" variable, all together.

Please Login or Register  to view this content.

Yeah, I was thinking that. I had starting adding With statements but got sidetracked.

Appreciate your help/efforts on this. Thanks a lot.