0

I've got a document with a sheet, where the real used range is A1:BJ36360, but a property UsedRange returns a range with address A1:BJ72724. A range A36361:BJ72724 is empty, there's no values, no formattings, and no formula is addressing any cell from it. I can do:

Range("A36361:BJ72724").Clear
Range("A36361:BJ72724").ClearFormat
Range("A36361:BJ72724").Clear<whatever the method with Clear at the beginning there is>
Range("A36361:BJ72724").Delete

But UsedRange remains the same A1:BJ72724. And if only I run:

Range("A36361:BJ72724").Delete Shift:=xlShiftUp

then UsedRange returns the expected range A1:BJ36360.

What could be the reason of this behaviour? How exactly UsedRange is calculated?

p.s. Office 365, Excel version 16.0

Vitalizzare
  • 4,496
  • 7
  • 13
  • 32
  • 4
    yup that is a known issue. Which is why using UsedRange to accurately find the last row or column is problematic and to be avoided. – Scott Craner Sep 29 '22 at 14:02
  • 2
    I guess `UsedRange` was/is even calculated differently on different Excel versions. I remember saving the file was updating UsedRange, but on my current version (365) it is updated immediately when clearing the last used cell - at least for the small example I just did. Long story short: Don't use `UsedRange`. Have a look to https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba to see for alternatives – FunThomas Sep 29 '22 at 14:08
  • @FunThomas could it be that they finally fixed it? I too cannot make it do what it did in previous versions. – Scott Craner Sep 29 '22 at 14:24
  • Maybe. But I would not rely on it. – FunThomas Sep 29 '22 at 14:43

1 Answers1

-1

I use this routine, based on info at https://www.rondebruin.nl/win/s9/win005.htm

Sub sub1() ' demo the routines
  Dim zRow&, zCol&
  Call LastCellActive(zRow, zCol)
  Debug.Print zRow; zCol
End Sub

Sub LastCellActive(zRow&, zCol&) ' last row, col in Activesheet
  ' https://www.rondebruin.nl/win/s9/win005.htm
  On Error Resume Next ' zeros if blank sheet
  zRow = Cells.Find("*", Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
  zCol = Cells.Find("*", Range("A1"), xlFormulas, xlPart, xlByColumns, xlPrevious, False).Column
End Sub
dcromley
  • 1,373
  • 1
  • 8
  • 23
  • 1
    This does not answer the question of why. It only duplicates the answers given in the link provided in the comments: https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba Which if that was the answer to this question we would have closed as a duplicate. The question is, "Why does UsedRange give the wrong extents?" and not, "How to find the last row and column?" – Scott Craner Sep 29 '22 at 15:46