The VBA UsedRange property is a useful property to add to your VBA repertoire. It's commonly found in internet threads for finding the number of rows and columns on a sheet - and in proxy, the iteration counts for for-loops - and it's certainly useful for that.`Used Range`

also has some other interesting features and there are a handful of caveats to keep in mind. Let's check it out.

- Uses Range object
- The shape of the assortment
- Working with rows and columns
- Using the .Address property
- First row and first column

- Count rows and columns
- Last row and last column
- Counts for For-Loops

- Properties and methods
- Clear the range

- Navigate ranges
- Hidden changes

`Used Range`

Object

`Used Range`

is a special one**Reach**property that specifies the range of cells on a worksheet that has been used correctly. The truth is that certain changes to a cell also mark it as "used" according to VBA. We'll talk more about this in a few minutes, but a better description of the VBA UsedRange property is a*range of cells, on the specified sheet, that have content, formatting, comments, or certain other changes.*It's a great way to take more control over your spreadsheet's data.

Since`Used Range`

is owned by the**Reach**object, all properties and methods available for Ranges are also available for`Used Range`

. This includes the number of columns/rows, select, clear, format, and range navigation. In addition, you can set it as a named object for easier reference, such as:

`Afm bel If ReachSet bel = sheets("target sheet").Used Range`

Where

Set a named object, such as*Intelligence*, a valuable resource.**Keep in mind that objects don't update on the fly, so you'll need to reset it to get updated properties!**If you set`.Clearly`

method, your variable**NOT**update automatically. You need to set it up again.

To demonstrate what VBA UsedRange actually does, add a few values to a blank spreadsheet and run this short macro:

`Sub SelecteerUsedRange() ActiveSheet.Used Range.SelectEnd Sub`

This macro selects the used range on your spreadsheet. You can see VBA trying to calculate the first cell and the last cell of data in your spreadsheet and selecting everything in that range. Notice in the image below that eachheal the cellin the used range are also included when using the UsedRange property. Even though you might think so, the VBA UsedRange property doesn't just capture theunionof all cells containing data.

## The shape of the assortment

As we have just shown, VBA gives you no choice in the form of the range it covers`Used Range`

. It always produces a rectangle and starts in the top left corner and fans out to capture the bottom row and rightmost column. Although they define the UsedRange boundaries, the top left and bottom right cells may not contain any values.

For example the`Used Range`

is on this image

**Used range of unfilled cells**

Create powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering our free**VBA Developer Kit**can help. It's packed with VBA shortcuts to help you create your own macros like this - we'll send a copy, along with our**Big Book of Excel VBA Macros**, to your email address below.

## Working with rows and columns

`Used Range`

is useful for finding the first and last rows and columns used on a worksheet. By extension, it can be used to count rows and columns to helpiterate through for loops.

### The habits`.Address`

Property

One way to get the first row/column pair and the last row/column pair is to use the`.Address`

property of the UsedRange:

`Afm bel If ReachSet bel = sheets("target sheet").Used RangeDebugging.Print bel.Address`

This returns the string*Recall that Debug.Print prints to yourVBA Immediate window.*

### First row and first column

You can also easily find the first row and first column as a number using the`.Queue`

In`.Column`

characteristics:

`Afm bel If ReachSet bel = sheets("target sheet").Used RangeDebugging.Print bel.QueueDebugging.Print bel.Column`

This simply returns the first row number and first column number of the range (1 and 2 in our example), which corresponds to the

The number for the**last**row and column requires a bit more work. One way to get the last row and column of a UsedRange is to use the`graaf`

property.

## Count rows and columns

To get the number of rows and columns in the range, you can use`.Graaf`

`Afm bel If ReachSet bel = sheets("target sheet").Used RangeDebugging.Print bel.Rows.GraafDebugging.Print bel.Columns.Graaf`

This returns 25 and 4 in our example above.

### Last row and last column

If the first cell in your UsedRange is`.Graaf`

of the rows and columns equals your last used rows and columns.

`Afm bel If ReachSet bel = sheets("target sheet").Used Rangelast row = bel.Rows.Graaf '25lastCol = bel.Columns.Graaf '4`

However, this is often not the case.**If your data doesn't start at $A$1, this method will not work!**

As shown in our example image, the last row is indeed Row 25. However, the last column is*not *Column 4 (D). It is column 5 (E). To be more robust, add the first row/column numbers to the row/column counts.

`Afm bel If ReachSet bel = sheets("target sheet").Used Rangefirst row = bel.Queuefirst Col = bel.Columnnumber of rows = bel.Rows.GraafnumberCol = bel.Columns.Graaf'don't forget to subtract 1 so as not to double count the first row/columnlast row = first row + number of rows - 1lastCol = first Col + numberCol - 1`

Now we get our expected result {25, 5} instead of {25, 4}. Don't forget to subtract 1 from the final result, otherwise you'll double count the first row and column.

Now that we've demonstrated the logic for calculating the last used row and column in a UsedRange,**let's show you a much easier way.**Use the following shortcut to include the last used row and column in a used range:

`Afm bel If ReachSet bel = sheets("target sheet").Used Rangelast row = bel.Rows(bel.Rows.Graaf).QueuelastCol = bel.Columns(bel.Columns.Graaf).Column`

### Counts for For-Loops

Please note our continued use of the`.Graaf`

property. The`.Graaf`

property is also useful when creating your for loops. Use the first row/column to start, then process an entire range down to the last row/column. For a simplified approach that only loops through the rows, let's say a sheet only contains numeric data in column A. You can store the running total of the numbers in column B (column 2) using a macro like this:

`Sub RunningTotal()Afm First row If Round number, Last row If Round number, I row If Round numberAfm bel If ReachSet bel = ActiveSheet.Used RangeFirst row = bel.QueueLast row = bel.Rows(bel.Rows.Graaf).QueueFor I row = First row Nasty Last row If I row = First row And Cells(I row, 2) = Cells(I row, 1) start with the running total Anders Cells(I row, 2) = Cells(I row, 1) + Cells(I row - 1, 2) 'add previous running total to new item End IfFollowing I rowEnd Sub`

You need two nested loops to loop through both the rows and columns in your used range, like this:

`Sub LoopThroughUsedRange()Afm First row If Round number, Last row If Round numberAfm First Col If Round number, LastCol If Round numberAfm I row If Round number, iCol If Round numberAfm bel If ReachSet bel = ActiveSheet.Used Range 'store the used range in a variableFirst row = bel.QueueFirst Col = bel.ColumnLast row = bel.Rows(bel.Rows.Graaf).QueueLastCol = bel.Columns(bel.Columns.Graaf).ColumnFor iCol = First Col Nasty LastCol For I row = First row Nasty Last row Debugging.Print Cells(I row, iCol).Address & " = " & Cells(I row, iCol) Following I rowFollowing iColEnd Sub`

If you only want to process data to cells in the UsedRange that are not empty, apply theVBA IsEmpty function.

## Properties and methods

Once you know how to navigate a UsedRange, applying properties with VBA is a breeze. Properties let you do things like change the entire range to bold font. Note that the properties are applied to the*whole*range, even if the cells are empty. It would be tedious to go back and correct the formatting for an entire sheet because of a single ill-thought-out line of code.

`Afm bel If ReachSet bel = sheets("target sheet").Used Rangebel.Font style.In bold = WHERE`

This macro fragment changes**all the cells**on the worksheet with data (or formatting plus empty cells between used cells) to bold. You can do the same with colors, cell fill colors,number formats, etc. Again, keep in mind that the property is applied to the entire range used, so if you have a nicely formatted table with 5000 cells, make sure you want all 5000 cells to be bold before running those lines of code !

### Clear the range

Another common use of`Used Range`

is todelete the contents of a sheet. If you use UsedRange you don't have to find the last row/column as above. After setting

`Afm bel If ReachSet bel = sheets("target sheet").Used Rangebel.Clearly or rng.ClearContents to clear data only`

Again, make sure you have the*whole*worksheet with formats and data. It's a bit like using it`sudo rm -r`

in Linux: powerful and convenient, but risky.

Create powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering our free**VBA Developer Kit**can help. It's packed with VBA shortcuts to help you create your own macros like this - we'll send a copy, along with our**Big Book of Excel VBA Macros**, to your email address below.

## Navigate the range

Reaching gives access*compared to the range itself*as opposed to relative to the worksheet. For example, in our table above, the first column is actually B, not A. If we knew the column format of the table - axis name, rank, country, population - we could refer to

- population column relative to leaf = 5
- population column against range used = 4

Let's imagine a user shifts the table to a different position on your worksheet for some reason. If you hard coded your macro to collect the population relative to the leaf, you may now accidentally get the wrong data. It would be more robust to use the table's own structure as a reference point:

`Afm bel If ReachSet bel = sheets("target sheet").Used Rangebel.Columns(4).Font style.In bold = WHERE`

For example, if the user moves the starting point of the table to $H$15, we can still make the population column bold, because the population column is still the 4th column in our UsedRange.

## Hidden changes

There is at least one caveat to its use`Used Range`

and it is important to consider. The caveat is about unseen changes. Cells that have been changed invisibly, such as with number formatting, font changes, or even comments, are included in UsedRange's opacity. What that means is that if a user accidentally italicizes a cell without any data, that cell still counts toward the "used range," so your UsedRange may be larger than you thought it would be. This expanded range would appear upon selection`Used Range`

and it would be included in the`.Graaf`

In`.Address`

characteristics.

This isn't always a problem, but it can easily be overlooked. In our`Ctrl+i`, we get this strange output in column B, which is sure to confuse the user:

**Format changes affected VBA UsedRange**

There is no obvious change in the cell`Used Range`

takes into account the formatting change and includes the entire area of

`Used Range`

makes it easy to find the first row/column and apply formatting to usable cells on a sheet. It also makes it pretty easy to find the last row/column, and most importantly it helps you set up your for loops.

If you found this tutorial helpful, please subscribe using the form below and we'll share similar VBA tips to help you get the most out of the programming language.

Ready to do more with VBA?

We've put together a giant PDF with over 300 pre-built macros and we want you to get it for free. Enter your email address below and we'll send you a copy with us**VBA Developer Kit**, packed with VBA tips, tricks, and shortcuts.

*This article was written by Cory Sarver, a contributing writer for The VBA Tutorials Blog.**Visit himLinkedInAnd hispersonal page.*