Countifs on only visible data after filtering

  • Do you mean there was an error when using it in your actual file?


    If so, what error on what line of code (when you get the error click "Debug" and let me know the line of code that is highlighted)


    With your actual file are rows 3 and 4 completely empty?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • It worked perfectly this time......I must have done something wrong.


    Rows 4 & 5 are empty...but I suppose that doesn't matter now it works.


    Thank you very much again...it's excellent :)

  • You're welcome.


    If row 5 is not empty then the .CurrentRegion used in the code would not work correctly.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • KjBox

    Hi KJ, I got your code working beautifully.....but I had to put Option Base 1 at the start of all my procedures for it to work. The reports are spot on but i was just checking with you to make sure it doesn't affect the rest of my codes....they are very basic without arrays etc so i assume it will not affect them. Cheers.

  • No, Option Base 1 will not affect any code that does not have arrays

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You're welcome.


    The increase speed is due to the use of arrays. All the data is loaded into an array (x) then the code manipulates the values in the array and builds another array (y), before placing the values in array y onto the second sheet. All that is done within the machine memory, there is no need for the code to have to constantly refer back to the worksheet to get new data and then paste the values after using your Countas function back to the worksheet.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • KjBox Hi KJ, I am using your codes on some other macros but I need some spaces (rows) between some of the results. Is it OK to just run the code on a blank sheet and then insert the rows in the middle of the results where I need them and then build the report template around the results.....will that cause any problems with the results from the macro? Cheers Cobber

  • Difficult to say without knowing what you need to do and what the report needs to look like.


    It would probably be better to adjust the code to include the empty rows.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • KjBox

    Hi, I have attached the workbooks for you to check.


    Monthly report needs spaces between rows 8-11, 14-17, 19-22, 23-26, 26-29.

    Notice report between 9-17, 24-32. You will see on the templates.


    Thanks again, don't worry if it's too much trouble :)


    If you think it would be OK to just run the code on a blank sheet and insert the rows after, I will do that.

    KJBox Monthly Report Code 21-11-19.xlsmNOTICE REPORT KJBox.xlsm

  • Thanks for those.


    Tied up with something else right now but I will take a look at them tomorrow.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!