0

I have a macro to count the number of used rows in a sheet and print the count in a cell in another sheet

Activesheet.Range("O9") = (Worksheets("Another_Sheet").UsedRange.Rows.Count) - 1

every time the values in "Another_sheet" will be cleared Worksheets("Another_Sheet").UsedRange.Clear and new set of rows will be printed and Activesheet.Range("O9") will hold the new value.

But the problem is, even if there are no values printed in "Another_Sheet", macro still prints number of rows that were there earlier and got cleared

I also tried Worksheets("Another_Sheet").UsedRange.SpecialCells(xlCellTypeLastCell).Row

braX
  • 11,506
  • 5
  • 20
  • 33
Jey.Guru
  • 1
  • 5
  • 1
    This post will show you best method [Post Link](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – Harun24hr Dec 27 '19 at 06:35
  • As a Stop gap, I am check if A1 of "Another_Sheet" has value and count used rows only if A1 is not empty – Jey.Guru Dec 27 '19 at 06:36
  • You need to save your workbook and reopen it, or else `rows.count` will count the rows in the used range that contained data. – GMalc Dec 27 '19 at 07:11

0 Answers0