Managing Your Spreadsheet With VBA UsedRange - wellsr.com (2023)

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 Rangealso 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 RangeObject

Used Rangeis a special oneReachproperty 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 arange 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.

SinceUsed Rangeis owned by theReachobject, all properties and methods available for Ranges are also available forUsed 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

Wheretarget sheetis the name of the sheet you want to work with.

Set a named object, such asbel, gives you accessIntelligence, 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 setbelthen change something like applying the.Clearlymethod, your variablebelshallNOTupdate 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 coversUsed 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 theUsed Rangeis on this imageB1:E25, although the four cells in the top corner, including cellB1, contain no data or formatting. Neither does cellE25. The same goes for the four cells at the bottom left. Importantly, the empty row between the top ten and the next ten entries, row 13, is also counted. Keep this in mind when using VBA UsedRange as the basis for counting rows and columns in your macro!

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 freeVBA Developer Kitcan help. It's packed with VBA shortcuts to help you create your own macros like this - we'll send a copy, along with ourBig Book of Excel VBA Macros, to your email address below.

Working with rows and columns

Used Rangeis 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.AddressProperty

One way to get the first row/column pair and the last row/column pair is to use the.Addressproperty of the UsedRange:

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

This returns the string$B$1:$E$25, which can be decomposed into the necessary pieces for the first row (1), the first column (B), the last row (25), and the last column (E).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.QueueIn.Columncharacteristics:

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$B$1cell we talked about earlier.

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

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$A$1, finding the last used row and column is easy because the.Graafof 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 isnot 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.Graafproperty. The.Graafproperty 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 thewholerange, 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 changesall the cellson 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 ofUsed Rangeis todelete the contents of a sheet. If you use UsedRange you don't have to find the last row/column as above. After settingbelas a named object simply run:

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

Again, make sure you have thewholeworksheet with formats and data. It's a bit like using itsudo rm -rin 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 freeVBA Developer Kitcan help. It's packed with VBA shortcuts to help you create your own macros like this - we'll send a copy, along with ourBig Book of Excel VBA Macros, to your email address below.

Navigate the range

Reaching gives accesscompared to the range itselfas 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 topopulationrelative to the blade or relative to the range.

• 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 useUsed Rangeand 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 selectionUsed Rangeand it would be included in the.GraafIn.Addresscharacteristics.

This isn't always a problem, but it can easily be overlooked. In ourRunningTotalFor example, if only the first 10 cells contained data, one would expect the total to be on row 10. However, if the user accidentally italicizes cell$ D $ 23with a slip on the keyboard andCtrl+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$ D $ 23, ButUsed Rangetakes into account the formatting change and includes the entire area of$A$1Unpleasant$ D $ 23.

Used Rangemakes 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.