UsedRange.Rows.Count shows different number than the actual

Question

I need some expert advice on VBA code. I have created a procedure to compare two short reports (the same format, generated from the system). I used the for loop starting from the first row until the last one using UsedRange.Rows.Count. The problem I have is that UsedRange.Rows.Count always return 30 even though the reports are generally shorter than that - usually around 20-25 rows. It returns 30 even if I clear the content inside these rows. It only returns the correct number when I permanently delete the blank rows up to row 30.

Why is that happening? It bothers me because also for some reason the macro highlights the blank rows (from the last actually used to row 30) showing them as a difference between the reports, while they exist and are the same (blank) in both.

Thank you

Answer 1

UsedRange will expand to include all cells that Excel registers as containing any data. Values, formulae, formatting — even (and I suspect that this may be what is happening here) a cell that has had a value deleted from it. Anything outside of UsedRange is treated as not yet officially existing yet.

As an analogy, this is the difference between an empty box, and no box (with the worksheet cells being boxes). UsedRange tells you where the last box is, even if you've emptied it (with Value = "" or ClearContent); only deleting the Rows/Columns/Cells will get rid of the box itself.

There are many much better ways to get the size of your data than by using UsedRange (although, it at least provides an Upper Bound)

For example, if your data is contiguous, then you can select a single cells (e.g. Range("A1") / Cells(1,1)), and get the CurrentRegion (Cells(1,1).CurrentRegion.Rows.Count). Or, if you know a column that always contains data, you can use End(..) to find the bottommost cell (Cells(Rows.Count,1).End(xlUp).Row)