Scriptorium

us fr nl




VBA: Ranges and cells

Using an UDF to find the last used row in a worksheet
Sometimes you want to know what the last used row in a worksheet is. There are two ways to interpret this question. 1) You really want to know the last used row in the worksheet, wether there is a value somewhere in that row or there isn't 2) You want to know the last row that contains a value in it somewhere.

Filtering unique values from a list of values #3
Yet another way to filter unique values from a list of values is by using the Dictionary object. Background: The Dictionary object is part of the Windows Scripting Host(WSH). Ever since Internet Explorer 3. 0 this is present on all Windows computers. WSH supports two types of scripting; VBScript (VBS) and JavaScript (JS). VBS provides two types of objects; the FileSystemObject object and the Dictionary object. The Dictionary object is very similar to the Collection object we know from VBA and it is designed to hold indexed lists of data in a manner similar to a Perl associative array.

Vertically reverse order of values in a selected range
In Excel you can easily sort data in columns but for reversing the order of the values there isn't a standard function available. This routine shows how you can quickly reverse the order of values in cells in a selected columns. For example, say you have these values in a worksheet: D5=1, D6=2 and D7=3. Using the routine below you would accomplish this: D5=3, D6=2, D7=1

Dumping large chunks of data from array to worksheet and vice versa
When you have to write a large amount of data from an array to a worksheet you could create a loop that writes all values to cells, one at the time. This is a rather slow process though. Fortunately there's a possibility in Excel to do this in a single statement. A very fast method, often used in conjunction with reading/writing from/to databases. The two sub routines below demonstrate this technique.

Filtering, counting and sorting large chunks of data
In some environments you are confronted with large amounts of data. If you have to process this data, e.g. sorting, filtering or ranking, it can be very time consuming going through all the cells. In these situations you best load all data in system memory, do your processing, and then write the results back to a worksheet, textfile or whatever you use. Another advantage of processing data in system memory is that you aren't limited by worksheet limits like the max. of 65535 rows. Got a database with 300,000 records that don't fit in a worksheet? just load them in system memory and work with them.

Filtering unique values from a list of values #1
Every now and then you want to create a list of unique values from a list where duplicate values (can) exist. For example, you might want to fill a combobox or listbox on a form with unique values. An easy way to create a list of unique values is by using a collection object. This sub procedure shows you how to do that.

Filtering unique values from a list of values #2
If you read Filtering unique values from a list of values #1 (http://scriptorium.serve-it.nl/view.php?sid=32) you know how to to create a list of unique velues from a list of values with duplicates. That method works well for ranges, lists and collections. However, if you have to substract the unique values from a range into another range, there is a faster and easier way to do that. This sub routine demonstrates how that works.

Check if the active cell is in a certain range
This sub procedure checks if the active cell is located in a predefined range.

Atom Feed
Contact | About This Application | Scriptorium Website