Calculate number of lines VBA
Solved
Fitz_chev
-
Fitz_Chev -
Fitz_Chev -
Hello everyone,
For a work project, I need to display the number of rows in my table at the end of the table. I have already managed to add my TOTAL under column A, my amount under columns M and N, but I need to put the number of rows contained in my table in columns 2 and 3. Of course, since this code is part of a larger macro, I would like to know the VBA code to calculate my rows. Thank you in advance.
Configuration: Windows 7 / Chrome 45.0.2454.85
For a work project, I need to display the number of rows in my table at the end of the table. I have already managed to add my TOTAL under column A, my amount under columns M and N, but I need to put the number of rows contained in my table in columns 2 and 3. Of course, since this code is part of a larger macro, I would like to know the VBA code to calculate my rows. Thank you in advance.
Configuration: Windows 7 / Chrome 45.0.2454.85
2 réponses
In fact, if you want the macro to adapt to your table even if it changes, I would personally start by integrating a variable that gives the last non-empty row of the table:
Then, for the sums you have already done, I invite you to reuse this code, for example the sum in A:
Next, to get the number of rows, you can do for example for column B:
Or for column C:
Sincerely.
Last_Row = Cells(Application.Rows.Count, 1).End(xlUp).Row 'Assuming column 1 is the most filled (the furthest)
Then, for the sums you have already done, I invite you to reuse this code, for example the sum in A:
Cells(LR + 2, 1).Value = Application.WorksheetFunction.Sum(Range(Cells(1, 1), Cells(LR, 1))
Next, to get the number of rows, you can do for example for column B:
Cells(LR + 2, 2).Value = Cells(Application.Rows.Count, 2).End(xlUp).Row
Or for column C:
Range(LR + 2, 3).Value = Cells(Application.Rows.Count, 3).End(xlUp).Row
Sincerely.