0

I have a worksheet I know is going to to contain a little bit of data, about 40 rows, which I need to clear out between runs. So I did this:

ThisSheet.Range("A2:ZZ999").Clear
ThisSheet.UsedRange.Calculate

I was under the impression that the second line would reset UsedRange and thus avoid the last row being a previous value. This appears to work.

However, when I look at the sheet in excel, it scrolls to line 999. The Calculate doesn't seem to fix this.

Did I miss something?

Maury Markowitz
  • 9,082
  • 11
  • 46
  • 98
  • 1
    What is you end goal? – BigBen Apr 04 '22 at 18:10
  • 1
    I seem to remember simply calling `ThisSheet.Usedrange` might have the functionality you're looking for. Can't confirm right now but worth a try. – Michael Murphy Apr 04 '22 at 18:18
  • 1
    Keep in mind that `UsedRange` has [issues](https://stackoverflow.com/a/11886420/4717755) – PeterT Apr 04 '22 at 19:02
  • 1
    Re _I was under the impression that the second line would reset UsedRange_ sorry, but no. First, it only works if you apply it to `ActiveSheet`, secondly you can't use another layer after `UsedRange`. So it's just `ActiveSheet.UsedRange` (obviously activate the required sheet first). Then do your calculate separately – chris neilsen Apr 04 '22 at 19:57
  • @BigBen - If you have 10 rows of data and then clear down to 999, excel thinks the sheet has 999 rows in UsedRange. This is frustrating because the scroll bars now go WAAAAY past your data. – Maury Markowitz Apr 05 '22 at 19:17
  • @chrisneilsen - I don't think either of those points are true. .Calculate does indeed seem to work, and the value returned for ThisSheet.UsedRange.Rows.Count is 999 in spite of it not being the active sheet. – Maury Markowitz Apr 05 '22 at 19:21

0 Answers0