-1

I have a sheet that has rows added to it each month (10,000 and growing). I have a module that copies the rows for each State to it's own sheet. I'm trying to add a Totals sheet that has each State's row total. It needs to automatically update when rows are added to All States sheet. I have attached examples of what I have and what I'm trying to achieve. Attachments, (1) All States sheet, (2) Arizona, (3) California, (4) New Mexico. And (5), Totals sheet, I'm trying to add. All States sheetArizonaCalifoniaNew MexicoTotals Sheet

I've tried permutations of Sheet.Rows.Count in a For loop, but it hasn't come out right yet.

Curtis Martin
  • 1
  • it's been a while since I've dug into VBA, but I think you want to check out UsedRange for the sheet which should get you the row & column count for what cells are used in that sheet – Toni H Feb 22 '23 at 22:04
  • Use `COUNTA()` function. – Harun24hr Feb 23 '23 at 01:56

1 Answers1

0

You can use =COUNTIF(A:A,"<>")-1 so it'll count the number of rows in column A and either adjust the range accordingly or take away the number of header/total cells you have which in this case is the -1 in the formula

Kairu
  • 381
  • 1
  • 9
  • OK, that will work one time. I will be constantly adding dozens of new rows each month to the main sheet, which will propagate rows to each state's sheet. The Totals sheet needs to automatically get the new row count for each of the states sheet and update the row count for each state on the Totals sheet. – Curtis Martin Feb 23 '23 at 18:43
  • I have been tweaking this code, but can't get it work, – Curtis Martin Feb 23 '23 at 18:58
  • Sub TotalsRowCount () For Each sh In Activeworkbook.Worksheets sh.Name & sh.Cells(sh.Rows.Count, "A").End(xlUp).Row - 1 Next sh End Sub – Curtis Martin Feb 23 '23 at 19:03
  • So you are copying all the rows from each individual state to the 'All States' sheet? `=COUNTIF(A:A,"Arizona")` – Kairu Feb 23 '23 at 21:18