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 count of all populated rows on the Andrew/Mohan worksheet to populate on the 'Index' worksheet under 'Overdue' & Mohan, then a count of all populated rows on the 'Arun' worksheet under 'Overdue' and so on until all of the sheets are checked and the 'Index' worksheet is populated. Then the same for 'Upcoming'. At the moment I am having to use a formula on each line on the 'Index' worksheet and change it based on what is column B (=COUNTA(Mohan!$A$2:$A$40)The data on the other sheets has to start from cell A2 and count until a row is empty. However, there may not be a separate worksheet for each person every week, so the loop has to recognize that there is no sheet for a persons name, where that is the case each week.
- 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.
Hi ClaireMcF ,
I'm sending you a file to you test if the Macro works for you. The code is the following:
Sub count_indexes() Dim ws As Worksheet 'All Worksheets variable Dim CurrentSheetName As String 'Variable to store the name of the Sheet that the program is reading Dim CurrentSheetCount As Integer 'Variable to store the qty of rows of the Sheet that the program is reading Dim RowToInsert As Integer 'Variable to store the row to be filled in INDEX Sheet RowToInsert = 3 'Initially we set the row to be filled in INDEX Sheet as 3. Feel free to change it according to your use case For Each ws In Worksheets 'Loop to run all of the Sheets from the file If ws.Name <> "INDEX" Then 'Apply this block of code only if the current Sheet is not INDEX CurrentSheetName = ws.Name 'Store the Sheet name (note that, in your print, the stored name would be 'Andrew_Mohan', and not 'Mohan' Sheets(CurrentSheetName).Activate 'Activate the Sheet Range("A2").End(xlDown).Activate 'Check the range with populated rows CurrentSheetCount = ActiveCell.Row - 1 'Store the row number and subtract 1, as we have to discount the "A1" cell Sheets("INDEX").Activate 'Activate Index Sheet Cells(RowToInsert, 2).Value = CurrentSheetName 'Store Sheet name into column B (you can change it according to your use case) Cells(RowToInsert, 3).Value = CurrentSheetCount 'Store the qty of populated rows into column C (you can change it according to your use case) RowToInsert = RowToInsert + 1 'Increase the row counter for the next run of the loop End If Next ws End SubYou probably will have to make some changes to adapt it to your use case, so I added some comments to make this task easy. Please, let me know if it works for you and, if you have any question or need any help for change it, let me know.
- Hi rzaneti
Thanks for your reply - this is really helpful. I have tried to run the code on my workbook, but I am getting a debug error at the :
CurrentSheetCount = ActiveCell.Row - 1 'Store the row number and subtract 1, as we have to discount the "A1" cell
and the code does not execute.
Can you help at all please?
Thanks
ClaireHi, ClaireMcF .
There are some possible reasons for an error at this line of code. Could you please send an image of the error message?
One of the possible problems is the error 6 (overflow), which will happen if we have an empty Sheet or if you have a specific Sheet with a large number of rows (more than 32,767 rows). Can you confirm if you have any of these cases in your file?
