Forum Discussion
Loop Through Worksheets and Counts Populated Rows
I want to create a macro that I can run each week. I have a workbook and on the first worksheet I want to count how many populated rows there are on the other worksheets. So in this example I want a...
If you're using Excel 365, the solution is simple (VSTACK and a few other functions) and does not require a line of code.
- Thanks Patrick - can you elaborate please? The simpler the better for me, as I am by no means an excel expert. Thanks in advance.
The formula would look like something like this:
=LET(Stack,VSTACK('A:C'!$A$2:$C$100000),filtered,FILTER(Stack,TAKE(Stack,,1)<>""),ROWS(filtered))In this demo, I have sheets A, B, C. Please see attached workbook.
- So that works for me, but for one sheet only. I had replaced 'A:C' with the name of my first worksheet (Mohan) to the name of my last worksheet (Terry), so it reads =LET(Stack,VSTACK'Mohan:Terry''!$A$2:$C$100000),filtered,FILTER(Stack,TAKE(Stack,,1)<>""),ROWS(filtered)) and I get a #NUM error. If I just use Mohan, then it works but I have to replace the name as I move down each row
