Elderberry Excel Macro Package

Rearrangement Macros
AmalgamateRows()Amalgamate groups of two or more rows by appending cells from subsequent rows to the end of the first row in the group.
SplitRows()Split rows into two or more at specified columns.
SplitCol()Split column if a particular string occurs within a cell in the specified column.
Tabulate()Create a new worksheet, tabulating values in the current sheet by key (new row id) within category (new column heads).
HTMLTable()Create a new worksheet, converting the current sheet to an HTML table.
Copy Macros
CopyCellsIfEmpty()Fill empty cells across a range of columns by copying values from the row above.
CopyRangeIfEmpty()Fill cells across a range of columns by copying values from the row above only if the first cell in the range is empty.
CopyRangeIfValue()Fill cells across a range of columns by copying values from the row above if the first cell in the range contains a particular value.
CopyDown()Copy formula or value from the selected cell down the rest of the column.
Delete Macros
DelAltRows()Delete alternate rows (or every nth row).
DelOddRows()Delete all odd numbered rows.
DelEvenRows()Delete all even numbered rows.
DelEmptyRows()Delete any row which is completely empty.
DelDupRows()Delete any row which is identical to the one above.
DelDupValues()Delete any row where specified columns are identical to the row above.
DelIfContainsString()Delete any row where a particular string occurs within a cell in the specified column.
DelIfExcludesString()Delete any row where a particular string does not occur within a cell in the specified column.
DelIfEmpty()Delete any rows where the specified column is empty.
DelIfNotEmpty()Delete any rows where the specified column is not empty.
DelIfNull()Delete any row with a null value in the specified column.
DelIfNumeric()Delete any row where the specified column is numeric.
DelIfNotNumeric()Delete any row where the specified column is not numeric.
DelIfNumericValue()Delete any row with a particular numeric value in the specified column.
DelIfNotNumericValue()Delete any row without a particular numeric value in the specified column.
DelIfStringValue()Delete any row with a particular string value in the specified column.
DelIfNotStringValue()Delete any row without a particular string value in the specified column.
DelUniqueRows()Delete any row which is not identical to the one above.
DelFromRow()Delete rows from active row to specified row (default end of sheet).
DelToRow()Delete rows from specified row (default start of sheet) to active row.
Key Macros
KeyCount()Count the number of unique values in a specified column.
KeyOccurs()Count occurrences of unique keys in the specified column.
KeySum()For each unique key in one column, sum the values in another column.
KeyAverage()For each unique key in one column, average the values in another column.
KeyLookUp()Associate values with a key field by looking up the key in another spreadsheet and copying one or more columns of details.
KeyLookUp2()Associate values with a key (2 columns) by looking up the keys in another spreadsheet and copying one or more columns of details.
Conversion Macros
ConvertKb()Convert Kb,Mb, Gb (as in file sizes) to integer Kb.
ConvertNeg()Convert overpunched negatives (e.g. from old mainframe systems) to leading minus signs.
PenceToPounds()Convert Pence to Pounds or Cents to Dollars (i.e. divide by 100). Overpunched negatives will be converted.
TrimValues()Trim leading/trailing spaces from cell values
Date Macros
DatePlusOne()Add one to dates. This gets round the problem that Excel (incorrectly) treats 1900 as a leap year whereas other applications don't, so dates imported in "days since the turn of the century" format will be one day out.
NumberToDate()Add separators to numeric dates.
TodaysDate()Insert today's date.
Display Format Macros
ToggleCase()Toggle selected cell(s) between upper, lower and title case.
ToggleDateFormat()Toggle selected cell(s) between dd/mm/yy and dd/mm/yyyy or mm/dd/yy and mm/dd/yyyy.
ToggleNumFormat()Toggle selected cell(s) between integer and 2 decimal places.
SortCodeFormat()Show number in bank sort code format (99-99-99).
Worksheet Formatting Macros
CopyColWidths()Copy column widths from the active sheet to the other sheets in the workbook.
NewFormattedSheet()Create an empty worksheet with the same formatting and column widths as the active sheet.
NumberRows()Put sequential row numbers in a specified column.
Header()Short-cut to creating headings for printing.
Footer()Short-cut to creating a footer for printing.
Worksheet View Macros
ToggleHideCols()Toggle hidden/visible status of selected columns.
UnhideAllCols()Show all hidden columns
Miscellaneous Macros
ExtractPostCode()Extract postcodes (UK) from other address fields into a separate column.
Utility Routines
These are subroutines called by some of the above macros but may be used independently.
CalcManual()Switch to manual recalculation (speeds up execution of macros).
CalcAuto()Switch to automatic recalculation.
CalcAsWas()Revert to previous recalculation mode.
ColNo(a)Convert column letter to numeric.
Cola(n)Convert column number to alphabetic.
DoW(n,full)Convert day of week (1-7) to short or full day name.
TCase(x)Convert a string to title case.
PromptBox(prompt, title, default)Prompt for macro parameters.