Microsoft Excel Macros

I've discovered beauty in Excel macros. Basically, a macro is a piece of code that is similar to a "rule" in Novell GroupWise (if you're unlucky enough to have to read your e-mail using this verbose and bloated software program). There is a subset of very smart people out there who write and make available bits of macro code that perform functions in spreadsheets. Many times these macros are written in VB, but macros can also be written in other languages, I think.

For example, this past summer I was trying to export data located in an Excel spreadsheet as a delimited text file that I could in turn import into a MySQL table. The data consisted of grade 9-12 content standards provided by the California Dept. of Education in case you were interested. Anywhooo... the data had all these carriage returns that kept screwing up the formatting in my exported text delimited file. I searched for a macro that would remove these annoying carriage returns in the troubled cells and came across a great little macro written by Chip Pearson (http://www.cpearson.com/excel.htm that does just that. The code is listed below.

You can create macros using the Visual Basic bundled with MS Office applications. Here's how you do it (these instructions were found here). Now, I'm not a VB programmer by any means, but I am MORE than happy to search, copy and paste code into Visual Basic that was written by other "brainiacs" if it will significantly improve my life.

Here's how you create a macro using Microsoft Visual Basic:

1. On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor.
2. On the Insert menu, click Module.
3. Type or copy your code into the code window of the module.
4. If you want to run the macro from the module window, press F5.
5. When you're finished writing your macro, click Close and Return to Microsoft Excel on the File menu.

The following procedure written by Chip Pearson removes tabs and carriage returns from cells in the active worksheet.

Sub CleanUp()
Dim TheCell As Range
For Each TheCell In ActiveSheet.UsedRange
With TheCell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Clean(.Value)
End If
End With
Next TheCell
End Sub

If you're still not 100% sold on macros, let me give you another example. Recently, I had data I exported from a MySQL table that I needed to import into Excel. After importing and cleaning up the spreadsheet, I found myself with a host of blank rows that I needed to delete before e-mailing the spreadsheet off to a colleague. Rather than engaging in the tedium of deleting these rows one by one like a "basic" Excel user, I decided to use another macro written by Mr. Pearson entitled DeleteRowOnCell found here.

Chip writes, "The macro DeleteBlankRows will delete a row if the entire row is blank. This macro will delete the entire row if a the cell in the specified column is blank. Only this column is checked. Other columns are ignored. This macro was suggested by Dana DeLouis, in the Excel programming newsgroup. Thanks, Dana !

Public Sub DeleteRowOnCell()

On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange

End Sub

To use this macro, select a columnar range of cells, and then run the macro. If the cell in that column is blank, the entire row will be deleted. To process the entire column, click the column header to select the entire column."

Good, good stuff. I'm feeling more and more like an Excel POWER USER every day thanks to Mr. Pearson. Thanks, Chip!!

Here are a few other cool and useful Excel tips and tricks!

Dynamic Numbering and Dynamic "Filtered" Numbering
=ROW() - 8 (number of rows down from the top of the spreadsheet)

Alternate Row Colors or Color Banding
Conditional Formatting > Formula is > =MOD(ROW(),2

Proper Text Change (will covert an entire list of names and addresses or other text from all UPPERCASE to Proper Case)

Macro to rearrange numbers in a cell