Code Sample Pages

Format SSN -- formulas and VBA solution for formatting Social Security Numbers

Sample Outlook Automation -- automate Outlook from Excel

To read about early & late binding, check out the Early/Late Binding page.

Automate Internet Explorer added 2/5/2008

Calculation Mode and Code Optimization added 2/8/2008

Creating Data Queries in Excel added 3/21/2008

Case changing in Excel VBA added 3/26/2008

Automated Word Mail Merge From Excel added 3/29/2008

UDF To Send Email from Worksheet added 3/30/2008

Changing State Names To Abbreviations in Excel using VBA -- NEW! added 4/2/2008

Latitude/Longitude Functions in Excel -- NEW! added 4/3/2008

Using varType Function in Excel VBA -- NEW! added 4/8/2008

Code Samples

Fix Badly Imported Formulas

If you have imported formulas from another program, you might have run into this problem where an apostrophe gets added to the beginning of each formula; so instead of viewing the results of the formula, you are viewing the formula itself. This code will fix that; just highlight the offending data and run.

Sub fixformulas()
'
' if you have some formulas with a ' or some other character in the beginning
'
Dim cell As Excel.Range
For Each cell In Selection
    cell = "=" & Right(cell, Len(cell) - 1)
Next cell
End Sub

FaceID Toolbar Generator

This code will create three toolbars with the first 1500 faceIDs. What are FaceIDs? They are numbers representing small icons which are used in toolbars and menus. For example, the little "diskette" 'Save' button is FaceID #3. I don't know how high the FaceID list goes, but you can certainly extend this code and see how far you can get!

Sub FaceIDList()
'
' creates a cool toolbar list of faceIDs with their corresponding numbers
'
'
Dim cmdbar As CommandBar
Dim cmdbtn As CommandBarButton
Dim objButton As CommandBarControl
Dim i As Long

Application.ScreenUpdating = False
    ' delete them in case they are still lingering
   On Error Resume Next
   Application.CommandBars("Icons 0 through 500").Delete
   Application.CommandBars("Icons 501 through 1000").Delete
   Application.CommandBars("Icons 1001 through 1500").Delete
   On Error GoTo 0
    
Set cmdbar = Application.CommandBars.Add(Name:="Icons 0 through 500")
cmdbar.Visible = True

    For i = 0 To 500
        Application.StatusBar = "Creating Face ID Button " & i & " out of 500"
        Set objButton = cmdbar.Controls.Add
        With objButton
            .Style = 3
            .Caption = i
            .FaceId = i
            .Visible = True
            .TooltipText = "Face ID: " & i
        End With
    Next i

Application.StatusBar = False
Set cmdbar = Application.CommandBars.Add(Name:="Icons 501 through 1000")
cmdbar.Visible = True

    For i = 501 To 1000
        Application.StatusBar = "Creating Face ID Button " & i & " out of 1000"
        Set objButton = cmdbar.Controls.Add
        With objButton
            .Style = 3
            .Caption = i
            .FaceId = i
            .Visible = True
            .TooltipText = "Face ID: " & i
        End With
    Next i

Application.StatusBar = False
Set cmdbar = Application.CommandBars.Add(Name:="Icons 1001 through 1500")
cmdbar.Visible = True

    For i = 1001 To 1500
        Application.StatusBar = "Creating Face ID Button " & i & " out of 1500"
        Set objButton = cmdbar.Controls.Add
        With objButton
            .Style = 3
            .Caption = i
            .FaceId = i
            .Visible = True
            .TooltipText = "Face ID: " & i
        End With
    Next i

Application.StatusBar = False
Application.ScreenUpdating = True
Set objButton = Nothing
Set cmdbar = Nothing
End Sub

Download FaceID toolbar generator code here.

Random Date Select

This macro uses a blank throwaway worksheet to choose a random date in between two dates that you specify. Please note this code is a bit old and wouldn't be the way I do it today, it is just posted here for illustration of various VBA techniques you may be able to apply to your own project.

Sub RandomDateSelect()

Dim cell As Range, c As Excel.Worbook
Dim X As String, count As Integer
Dim StartDate As Date, EndDate As Date
Const APPNAME As String = "Random Date Select"

Application.ScreenUpdating = False

Set c = Workbooks.Add

X = InputBox("Enter start and end dates" & vbCr & vbCr & _
	"Enter dates separated by commas, i.e." & vbCr & _
	"10-1-2003, 12-1-2004", APPNAME)
If X = "" Then Exit Sub

With WorksheetFunction
   StartDate = CDate(Trim(Left(X, Find(",", X) -1)))
   EndDate = CDate(Trim(Mid(X, .Find(",", X) + 1)))
End With

If StartDate > EndDate Then Exit Sub

count = EndDate - StartDate

' fill cells with consecutive dates
For i = 1 To count + 1
   Cells(i, 1).Value = StartDate
   StartDate = StartDate + 1
Next i

Range(Selection, Selection.End(xlDown)).Select

Selection.Offset(0, 1).Select
Selection.FormulaR1C1 = "=RAND()"

With Selection
   .Copy
   .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, _
	Transpose:=False
End With

Range(Selection.End(xlToLeft), Selection.End(xlDown)).Select
Selection.Sort Key1:=Selection.End(xlToRight), Order1:=xlAscending, _ 
	Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
	Orientation:=xlTopToBottom

If Format(Cells(1, 1).Value, "dddd") = "Saturday" Then
   MsgBox (Cells(3, 1).Value & " was randomly selected.")
ElseIf Format(Cells(1, 1).Value, "dddd") = "Sunday" Then
   MsgBox (Cells(2, 1).Value & " was randomly selected.")
Else
   MsgBox (Cells(1, 1).Value & " was randomly selected.")

c.Close savechanges:=False

Application.ScreenUpdating = True

End Sub

Convert Phone Numbers

I use the following code to put phone numbers into a standard format. This will remove punctuation and most odd characters people usually put in phone number cells. You will have to remove any suffixes such as extension before running this code.

Sub Convert_Phone()
Application.ScreenUpdating = False
'
' first highlight the section you want to work on
'
With Selection.SpecialCells(xlConstants)
   .Replace what:=Chr(160), Replacement:="", LookAt:=xlPart, _ 
	SearchOrder:=xlByColumns, MatchCase:=True
   .Replace what:=Chr(32), Replacement:="", LookAt:=xlPart, _
	SearchOrder:=xlByColumns, MatchCase:=True
   .Replace what:=")", Replacement:="", LookAt:=xlPart, _ 
	SearchOrder:=xlByColumns, MatchCase:=True
   .Replace what:="(", Replacement:="", LookAt:=xlPart, _ 
	SearchOrder:=xlByColumns, MatchCase:=True
   .Replace what:="-", Replacement:="", LookAt:=xlPart, _
	SearchOrder:=xlByColumns, MatchCase:=True
   .Replace what:="+", Replacement:="", LookAt:=xlPart, _
	SearchOrder:=xlByColumns, MatchCase:=True
End With

' at this point you could do one of two things:
' 1. do a "virtual" format where you just make the cell *appear* to be a 
' phone number.
' Selection.NumberFormat = "(###) ###-####"

' 2. We can actually insert the parentheses and dash in the appropriate place.
' 
' For each cell in Selection
'  cell = "(" & Left(cell, 3) & ") " & Mid(cell, 4, 3) & "-" & Right(cell, 4)
' Next cell
'
' uncomment whichever one you want!
'
'
Application.ScreenUpdating = True
End Sub

Delete Error Conditions

This will delete any cells in your selected range if the value is "#N/A".

Sub Clear_ISNA()
Dim cell As Excel.Range
Application.ScreenUpdating = False

For Each cell In Selection
   If WorksheetFunction.IsNA(cell) Then
      cell.ClearContents
   End If
Next cell

Application.ScreenUpdating = True
End Sub

Paste Values in Selected Cells

If you have a large block of formula cells you want to convert to values.

Sub Paste_Values()
Application.ScreenUpdating = False

With Selection
   .Copy
   .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, _
	Transpose:=False
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Delete Empty Rows

Highlight the block of cells you want to act on, otherwise it will act on the entire used range

Sub Del_Empty_Rows()
Dim R As Long
Dim rng As Range
Application.ScreenUpdating = False

If Selection.Rows.Count > 1 Then
   Set rng = Selection
Else
   Set rng = ActiveSheet.UsedRange.Rows
End If

For R = rng.Rows.count To 1 Step -1
   If WorksheetFunction.CountA(rng.Rows(R).EntireRow) = 0 Then
      rng.Rows(R).EntireRow.Delete
   End If
Next R

Application.ScreenUpdating = True
End Sub

Selective Autofilter

When you want your end users to only filter on one column. This code assumes that the data is in a contiguous text block and the headers are in row 1

Sub AutoFilter_Arrows_Hide()
Dim Col As Range
Dim i As Integer
Dim ShowCol As Integer
Application.ScreenUpdating = False

i = Cells(1, 1).End(xlToRight).Column

ShowCol = InputBox("Only allow filter in column number...")

For Each Col In Range(Cells(1, 1), Cells(1, i))
   If Col.Column <> ShowCol Then
      Col.AutoFilter Field:=Col.Column, visibledropdown:=False
   Else
      Col.AutoFilter Field:=Col.Column, visibledropdown:=True
   End If
Next Col

Application.ScreenUpdating = True
End Sub

Remove Hyperlinks

Highlight the block of cells you want to act on, then run this code

Sub Remove_Hyperlinks()
Application.ScreenUpdating = False

Selection.Hyperlinks.Delete

Application.ScreenUpdating = True
End Sub

Toggle personal.xls workbook hidden/visible

If you are lazy like me then you will appreciate this code. You cannot edit macros in a hidden workbook, so when you are debugging code you need to constantly hide/unhide this workbook. Very effective when you assign a hotkey to this macro. This code takes advantage of the fact that the Visible property of a Window is Boolean (True or False) so you can use the 'Not' keyword to switch the current value of Visible to its opposite, every time you run the macro. So when the personal workbook is visible, this hides it, and vice versa.

Sub Unhide_PERSONALXLS()

unhide = Windows("PERSONAL.XLS").Visible
Windows("PERSONAL.XLS").Visible = Not unhide

End Sub

Rename Worksheet

This code will take the name of the workbook and give the worksheet the same name. Makes your worksheets look slick!

Sub Rename_Sheet()

X = ActiveWorkbook.Name
X = Left(X, Len(X) - 4)
Sheets(1).Name = X

End Sub

Remove Zip Code Suffix (if found)

Highlight a mixed list of zip codes (12345 and 12345-6789) and run this code to remove the last 4. Remember if you run this code then any zip codes starting with '0' (NJ, MA, CT) will get trunc'ed.

Sub Fix_ZIP_plus4()
Dim cell As Excel.Range
Application.ScreenUpdating = False

For Each cell In Selection
   If Len(Trim(cell)) > 5 Then
      cell = Left(cell, Len(cell) - (Len(cell) - 5))
   End If
Next cell

Application.ScreenUpdating = True
End Sub

You could also do this directly in Excel with a formula: =IF(LEN(A1)>5,LEFT(A1,5),A1) Fill down as needed.

Spreadsheet Logging to CSV file

Here is a great macro you can use in your code to log selected information to a CSV file. For example if you need to log your variables, this macro will do it for you. Please note I did not write this, it was taken from this site.

Public Sub LogInformation(LogMessage As String)

Const LogFileName As String = "C:\Your FileName Here.csv"
Dim FileNum As Integer

FileNum = FreeFile
Open LogFileName For Append As #FileNum
Print #FileNum, LogMessage
Close #FileNum
End Sub

So in your code you could call this macro like

LogInformation Variable1 & "," & Variable2 & "," & Now

This would append some variabes and the date/time at the bottom of the CSV file you specified. If you wanted to update two separate log files, you could change the code to "Public Sub LogInformation(LogMessage As String, LogFileName As String)" and remove the "Const" line in the code above. Then just specify the full path and file name as such:

LogInformation "SomeInfo", "C:\FileName1.csv" and
LogInformation "SomeOtherInfo", "C:\FileName2.csv" to pass both arguments to each sub.

Merging Workbooks

This code will browse through a folder named "merged" on your desktop, and merge all of the workbooks together into one super workbook (one sheet) called "merged.xls". So for example if someone sent you 10 attachments in an Outlook email, you could click "Save Attachments" and create a folder called "merged", then run this macro to glue them all together. You could go even further and convert this to Outlook code, and automate the entire process.

Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
	(ByVal lpBuffer As String, nSize As Long) As Long

Public Function UserName() As String
Dim lpBuff As String * 1024
GetUserName lpBuff, Len(lpBuff)
UserName = Left$(lpBuff, (Instr(1, lpBuff, vbNullChar)) -1)
End Function

Sub MergeWorkbooks()
Dim NewWB As Excel.Workbook
Dim FName As String
Dim myLastCell As String, myLastRow As String, myLastColumn As Long
Dim myRange As String
Dim directoryfiles()
Dim count As Integer
Dim filen As String
Dim UserN As String, AddRange As Excel.Range
Dim i As Long

Application.ScreenUpdating = False

UserN = UserName

If Dir("C:\Documents And Settings\" & UserN & "\Desktop\merged.xls") <> "" Then
   MsgBox ("File already exists, clear it out before running this macro"), _
	vbCritical
   Exit Sub
End If

If Dir("C:\Documents And Settings\" & UserN & "\Desktop\merged\*.xls") = "" Then
   MsgBox ("No XLS files are in the MERGED directory." & vbCrLf & _
	"Put some workbooks there first."), vbCritical
   Exit Sub
End If

filen = Dir("C:\Documents And Settings\" & UserN & "\Desktop\merged\")

' build a list of workbooks
Do
   If filen <> "" Then
      ReDim Preserve directoryfiles(count)
      directoryfiles(count) = filen
      count = count + 1
   End If
   filen = dir
Loop While filen <> ""

Set NewWB = Workbooks.Add
Activeworkbook.SaveAs "C:\Documents And Settings\" & UserN & _
	"\Desktop\merged.xls", FileFormat:=xlNormal

Set AddRange = Workbooks("merged.xls").Worksheets(1).Range("A65536")

For i = 0 to UBound(directoryfiles())
   Workbooks.Open ("C:\Documents And Settings\" & UserN & "\Desktop\merged\" & _
	directoryfiles(i)

   ' delete extra rows to clean up file
   Dim R As Long
   Dim rng As Excel.Range
   
   Set rng = ActiveSheet.UsedRange.Rows

   For R = rng.Rows.count To 1 Step -1
      If WorksheetFunction.CountA(rng.Rows(R).EntireRow) = 0 Then
         rng.Rows(R).EntireRow.Delete
      End If
   Next R

   ' find used range
   myLastRow = Cells.Find("*", [A1],,, xlByRows, xlPrevious).Row
   myLastColumn = Cells.Find("*", [A1],,, xlByColumns, xlPrevious).Column
   myLastCell = Cells(myLastRow, myLastColumn).Address
   myRange = "A1:" & myLastCell
   Range(myRange).Copy Destination:=AddRange.End(xlUp).Offset(2,0)
   Workbooks(directoryfiles(i)).Close savechanges:=False
Next i

Workbooks("merged.xls").Close savechanges:=True

Set NewWB = Nothing
Set AddRange = Nothing

MsgBox ("Merge complete!" & vbCrLf & vbCrLf & UBound(directoryfiles()) + 1 & _
	" workbooks were merged."), vbInformation

If MsgBox("Would you like to delete the separate workbooks?") = vbYes Then
   For i = 0 To UBound(directoryfiles())
      Kill ("C:\Documents And Settings\" & UserN & "\Desktop\merged\" & _
	directoryfiles(i))
   Next i
   MsgBox ("Done!"), vbInformation
End If

Application.ScreenUpdating = True
End Sub

Buzzword Bingo

I created a Buzzword Bingo game. As some of you are aware, Buzzword Bingo (AKA Bullshit Bingo) is a great game to play if you work in an office where you have to attend boring meetings where managers use tired old business cliches constantly. You have to amuse yourself somehow, right? It works just like regular Bingo, except instead of letter/number combinations, you use management cliches.

First create a workbook with two sheets named "Table Generator" and "Data". On the "Data" worksheet, create two defined names:

"FullTable", Refers To: =OFFSET(Data!$A$1,1,0,COUNTA(Data!$A:$A)-1,COUNTA(Data!$1:$1))
"TableData", Refers To: =OFFSET(Data!$A$1,1,0,COUNTA(Data!$A:$A)-1,1)

These are dynamic ranges that will automatically expand/contract as needed.
In A1 and B1, create your headers (doesn't matter what you write). The buzzwords should go in column A, starting from A2.
On the "Table Generator" worksheet, highlight A1:E5 and apply some borders. In C3 you can type "Free Space".
Paste the macro code below into the ThisWorkbook code module for the worksheet.
Then, using the Forms Toolbar (View|Toolbars|Forms), create a button and label it "Bingo Card Generator" (or whatever you want). It should be placed somewhere outside the A1:E5 area. Right-click the button and choose "Assign Macro" and select the GenerateRandom() macro you just pasted.
Now select the "Data" worksheet, hide it (Format|Sheet|Hide) and click the button to generate the cards!

Sub GenerateRandom()
Dim i As Integer

Application.ScreenUpdating = False

Sheets("Data").Activate
With Sheets("Data")
    .Range("TableData").Offset(0, 1).Formula = "=RAND()"
    With .Range("TableData").Offset(0, 1)
        .Copy
        .PasteSpecial Paste:=xlValues, Operation:=xlNone, skipblanks:=False, _
		Transpose:=False
    End With
    .Range("FullTable").Sort key1:=Cells(1, 2), order1:=xlAscending, _
	header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Sheets("Table Generator").Activate
With Sheets("Table Generator")
 
For i = 1 To 5
    .Cells(1, i) = Sheets("Data").Range("TableData").Cells(i, 1)
Next i
 
For i = 1 To 5
    .Cells(2, i) = Sheets("Data").Range("TableData").Cells(i + 5, 1)
Next i
 
.Cells(3, 1) = Sheets("Data").Range("TableData").Cells(11, 1)
.Cells(3, 2) = Sheets("Data").Range("TableData").Cells(12, 1)
.Cells(3, 4) = Sheets("Data").Range("TableData").Cells(13, 1)
.Cells(3, 5) = Sheets("Data").Range("TableData").Cells(14, 1)
 
For i = 1 To 5
    .Cells(4, i) = Sheets("Data").Range("TableData").Cells(i + 14, 1)
Next i
 
For i = 1 To 5
    .Cells(5, i) = Sheets("Data").Range("TableData").Cells(i + 19, 1)
Next i
 
End With
 
Application.CutCopyMode = False
Application.ScreenUpdating = True
 
End Sub

Here is a sample workbook with the buzzword bingo game.

At work today I created a nice little formula to format Social Security Numbers (I work with them a lot). What it does is check a specific cell and, if necessary, converts it to a SSN ("###-##-####") by actually adding dashes to the value. Check it out:

=IF(ISERROR(FIND("-",A1)),IF(LEN(A1)=9,LEFT(A1,3)&"-"&MID(A1,4,2)&"-"&RIGHT(A1,4),A1),A1)

Basically, it does an unintelligent check to see if the value in A1 is already written as a SSN (by checking if the cell has a dash in it), and if not, it checks for the correct amount of digits and if there are 9 numbers, it adds the dashes in the appropriate places. If there are less than 9 numbers, or if there is already a dash in the cell, it simply returns the value of the cell.

Here is a screenshot:

SSN Excel Formula

You could get as complicated as you want with this, for example you can add another condition that if there are less than 9 numbers in the cell, pad the left side with zeroes until you have a valid SSN (I'll post that code eventually). You could also do this in VBA code.

Daily Time Log Workbook

You can create a useful workbook that helps you keep track of your schedule, using only built-in Excel functions! No macros are required. I created this workbook that can be used to track daily activities. For example if you have to keep track of time spent on a project (for billing purposes) or tell your boss what you were up to all day.

This workbook uses Data Validation, Dynamically Named Ranges, Custom Cell Formatting and Array Formulas to calculate elapsed work times.

Daily Time Log Workbook

First, name your worksheet ‘Time Log’ by double-clicking the sheet tab name.

Next, the following named ranges should be created (Ctrl-F3 or Insert>Name>Define):

Name: Activity
Refers to: ='Time Log'!$B$6:$B$30
Name: Duration
Refers to: ='Time Log'!$E$6:$E$30
Name: End
Refers to: ='Time Log'!$D$6:$D$30
Name: ListRange
Refers to: =OFFSET('Time Log'!$L$2,0,0,COUNTA('Time Log'!$L:$L),1)
Name: Start
Refers to: ='Time Log'!$C$6:$C$30

Now place a list of work activities in column L, starting in cell L2:

Email Queue
Break
Lunch
Meetings
etc...

Whatever you might work on during any given day.

Highlight B6:B30 and click on Data>Validation. Select ‘List’ in the Allow: dropdown, for the Source, enter =ListRange.

Daily Time Log Workbook

This will tie your activity list in column B to the list you created in column L, so you can select activities from a dropdown box instead of typing them (and potentially mistyping and causing errors in the calculation formulas). You can add/remove entries in column L and they will automatically be added to the validation dropdown in cells B6:B30.

Highlight cells C6:D30 and format as Time. Then place this formula in cell E6:

=IF(NOT(ISBLANK(D6)),End-Start,"")

Format cell as Custom, enter h "hours, " mm "minutes" in the Type box.

Cell E6 should be filled down to E30.

Now in another cell (I chose H9) place the following formula:

="Total time logged today: "&TEXT(SUM(Duration),"h:mm")&" hours"

This formula will auto-total the entries in column E to give you a sum of how much you have logged.

To get a running total of the time spent on each activity, use the following formula:

=SUM(IF(Activity=,Duration))

Where is the name of one of the activities you entered in column L. This formula must be committed using Ctrl-Shift-Enter, since it is an array formula.

If you create a separate array formula for each activity, you can get a running total of each activity, for example see H12:I23 in the sample Daily Time Log Workbook. As you add and repeat activities at different times during the day, these formulas calculate running totals.

If you are satisfied with all of the entries in column L, you can safely hide this column (select a cell in the column, go to Format>Column>Hide).

To wrap this up, select a cell at the bottom of the data area (I chose A33) and go to Window>Freeze Panes. This keeps the work area from being scrolled off the screen by mistake.

To use the workbook, simply select a cell in B6:B30 and click the dropdown box to select the activity you want to document. Enter the start and end times (Time values must be entered in 24 hour format, i.e. 15:00 for 3:00 PM). The duration and running total formulas will auto-calculate to show you totals, so all you have to do is enter the activity, start and end time.

Download sample workbook here.

Create Default Workbook Template (book.xlt)

1. Create a blank workbook, customized with number of sheets, fonts, styles etc.
2. Hit F12 to save the workbook, in the 'Save As Type' dropdown, choose 'Template'
3. For the filename, type 'book' (to save the template as book.xlt in your XLSTART folder)
This will be the default workbook whenever you start Excel.

Create Default Worksheet Template (sheet.xlt)

1. Create a blank worksheet, customized with fonts, styles, zoom etc.
2. Hit F12 to save the workbook, in the 'Save As Type' dropdown, choose 'Template'
3. For the filename, type 'sheet' (to save the template as sheet.xlt in your XLSTART folder)

This will be the default worksheet whenever you Insert>Worksheet (or Shift-F11).

"Phantom macro" removal

If Excel is warning you about macros, but you are sure no macros exist in the workbook:
1. Open the offending workbook
2. Press Alt-F11 to open the VB Editor
3. Press Ctrl-R to open the Project Explorer (if not already open)
4. Double-Click on "VBAProject (workbookname)" and look for "Modules"
5. Double-Click on "Modules" and you should see an empty module there
6. Delete it, save and close.

Count Unique Values in a Range

Return a count of unique values (numbers only), do not count blanks/text - array formula (Ctrl-Shift-Enter):
{=SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,1))}

Return a count of unique text and number values (range cannot contain blank cells) - array formula (Ctrl-Shift-Enter):
{=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1))}

Returns a count of unique text and number values, does not include blanks - array formula (Ctrl-Shift-Enter):
{=SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""),
IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""))>0,1))}

Code Sample Pages

Code Samples

Fix Badly Imported Formulas

FaceID Toolbar Generator

Random Date Select

Convert Phone Numbers

Delete Error Conditions

Paste Values in Selected Cells

Delete Empty Rows

Selective Autofilter

Remove Hyperlinks

Toggle personal.xls workbook hidden/visible

Rename Worksheet

Remove Zip Code Suffix (if found)

Spreadsheet Logging to CSV file

Merging Workbooks

Buzzword Bingo

Daily Time Log Workbook

Create Default Workbook Template (book.xlt)

Create Default Worksheet Template (sheet.xlt)

"Phantom macro" removal

Count Unique Values in a Range

LAST UPDATED: April 8, 2008