UsedRange in Worksheet using VBA
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. |