UsedRange in Worksheet using VBA

usedrange

UsedRange returns a Range object representing the area of a worksheet that is being used. You will use the UsedRange to work with a range of cells in a worksheet whose size you have no control over. The UsedRange property represents the area described by the farthest upper-left and farthest lower-right used cells in a worksheet and includes all cells in between.

As an example, with the active workheet as in the image above, running the code 


?ActiveSheet.UsedRange.Address

in the Immediate window will return $B$2:$C$4. From this we can conclude that UsedRange:

  • is a rectangle range,
  • includes cells containing data or being formatted and possibly other things,
  • does NOT automatically include topleft cells such as A1,
  • does NOT consider the active cell as 'used'.
Warning
If you are only inserted in data you must be aware that not all rows actually contain data - there may be other reasons a row is included, as in the above example includes the formatted cell C4.