Formula and Worksheet Tips
- Select All Unlocked Cells
A VBA procedure to select all unlocked cells on the active worksheet. - Playing a Sound Based on a Cell's Value
A worksheet function that will play a WAV file, based on a condition you specify. - Using Controls on Worksheets
Discusses the differences between the two types of worksheet controls. - Synchronizing Sheets in a Workbook
A VBA subroutine that forces all sheets in workbook to have the same range selection and upper left cell as the active sheet. - Summing and Counting Using Multiple Criteria
Example formulas that demonstrate various counting and summing techniques. - Automatically Resetting the Last Cell
A simple procedure that forces Excel to keep track of the last non-empty cell in a worksheet. - Does a Worksheet or Workbook Have Code?
Excel 97 custom functions to determine whether the code module in a particular workbook or worksheet contains any VBA code. - Determining the Number of Printed Pages
How to determine how many pages will be printed. - A Custom Function For Relative Sheet References
A VBA worksheet function that lets you refer to worksheets in a relative manner. - Determining the Data Type of a Cell
A VBA worksheet function that returns a string that describes the contents of a cell. - Working With Names in VBA
Some VBA techniques that are useful when dealing with names. - Deleting All Empty Rows
A fast and efficient subroutine to delete all empty rows in a worksheet. - Cell Counting Techniques
Worksheet formula examples that demonstrate various ways to count cells that match a criteria. - Multifunctional Functions
A technique that makes a single worksheet function serve multiple purposes. - Creating a List of Formulas
VBA code for a utility that creates a concise listing of all formulas in a worksheet. - Extracting the n-th Element From a String:
A handy custom worksheet function that extracts a specified element from a text string. - Determining the last non-empty cell in a column or row:
Two useful VBA functions. LASTINCOLUMN returns the contents of the last non-empty cell in a column; LASTINROW returns the contents of the last non-empty cell in a row. - Creating a Worksheet Map:
A useful utility that creates a map for a worksheet. The map color-codes each cell by type (text, number, or formula). - Controlling User Scrolling:
How to hide rows and columns to prevent users from scrolling around unused areas of a worksheet. The technique also solves a problem that may cause the worksheet to scroll completely off the screen. - Naming Techniques:
Explains some interesting and useful techniques for creating names in your workbook. - Using Custom Number Formats:
Tips on creating and using your own number formats for cells. - Identifying Unique Values in an Array or Range:
a VBA function that accepts either a worksheet range object or a VBA array. The function returns either (a) a variant array that consists of just the unique elements in the input array or range, or (b) a single value - the number of unique elements in the input array or range.