Excel Geeking: Removing All Empty Sheets using VBA

You ever need to go through a workbook you’re working on and delete a bunch of sheets that have nothing in them? Maybe you’re creating a new automated report that requires you to plunk down a large chunk of data in a new workbook, but when you create that new workbook, it creates it with “Sheet1” and “Sheete2” and “Sheet3”. Yes, I know, you can change how many new sheets are present when you create a new workbook, but if you need to make your code portable, cause maybe somebody else will be running the report, and maybe that person has a backup in case they’re out with meningitis or something, and that person has an admin do it–well, you can lose track of your users pretty quick.

And let’s be honest: a report, or some other workbook, that has a bunch of “Sheet2″s, “Sheet3″s, “Sheet4″s, and so forth, in it looks pretty amateur.

Wouldn’t it be nice to have a nice clean little function that let’s you go through and blow away all the sheets in a workbook that are empty of data without having to do that manually? And that aren’t just named “Sheet1”, “Sheet2”, etc.? It sure would. But how might you do that?

Wonder no more!

Below is some code I worked up for just such a task. It bases its actions on whether the UsedRange property is a single cell, that cell being the very first cell in a worksheet. If that’s all there is to the UsedRange, then we’ll take a quick look at the length of the data in that cell. If the length of the data is zero, well then that cell is empty, and that means the sheet is empty. There for we can blow it away.

As with most of my code, there is probably a better way of doing this. But this is what I landed on and it works beautifully (for me, at least). If you have a better method, chime in, I’d love to see how you tackled it.

Sub RemoveEmptySheets()
'   This deletes any sheet that has nothing in it.

    ' Variable declarations.
    Dim sh  As Worksheet

    ' Loop through all the sheets in the active workbook.
    For Each sh In ActiveWorkbook.Worksheets

        ' Perform two tests: checking if the used range is the very first cell,
        ' then checking to see if the lenght of hte cell contents is zero, meaning
        ' the cell is empty.
        If sh.UsedRange.Address = "$A$1" Then
            If Len(sh.Range("A1").Value) = 0 Then

                ' Delete the sheet.
                Application.DisplayAlerts = False
                Application.DisplayAlerts = True

            End If
        End If

    Next sh

End Sub


Excel Geeking: When List Object Columns Attack, or Why Won’t This Calculated Column In My List Object Table Calculate Anymore

I know the title of this post is too long. But it captures the essence of what I’m trying to figure out.

I’m sure this has happened to someone else, and maybe even someone else who has figured out how to correct it. If you have, let me know, leave a comment below, send a smoke signal, anything to help me close the loop on this thing.

Here’s the problem: in a list object, or table as they’re often called, on occasion a field that is normally calculated stops doing just that. More clinically, the formula replication functionality in Excel coughs twice, falls down in the dusty road of the information highway, and gives up the ghost. Not on all tables, mind you. Not even on all columns in the same table. In specific columns only.

I’m on the hunt to figure out why.

The primary problem in searching for an answer is that you can’t quite find what you’re looking for through Google. Or maybe its just me, and that I could very easily suck and searching the interwebs. When I try searching for words such as “list object calculated column not working”, or “replicate formula no longer working”, I always find myself awash in a sea of posts about how to change the setting in Options for replicating formulas. That’s not what I need. I also find a lot of posts on how to turn Calculation back on in a workbook. Not what I need. I know these two things already. I need something else.

I checked to make sure that the cell isn’t formatted for Text. If you’ve ever formatted a column as Text and tried to change the formatting back to Number or General, you know that Excel can occasionally develop amnesia about how formulas work in those cells. Once you turn a cell to Text, changing it back is a pain in the butt given that Excel likes to keep treating formulas as if they’re Text. No, that’s not the issue I have here.

I tried adjusting the Options settings. I turned the Replicate Formulas option off, then back on. I had a hope that this would essentially pop the clutch on these calculated columns. No dice. I tried doing it through the code. Also no dice.

I could have spent all day rotting this thing out, but guess is that is never actually find it. It was simpler to build a new table with the same columns and move my data into it. Which is what I did.

Still, I figure there has to be a means of resetting a calculated column. Anyone? Anyone? Bueller? Bueller?

Excel Geeking: Extracting Comments Using VBA

A couple of months ago a colleague moved into a new position at work and inherited a big ugly spreadsheet. Happens to the best of us, amiright? The biggest challenge with this spreadsheet is that the previous owner, who has since retired (and good for him!) didn’t update his status notes and comments in a cell or a column. He kept them all in in-cell comments.

In. Comments.

Needless to say, the spreadsheet my colleague inherited had a couple of billion comments, some of which were absolutely enormous. She wanted to know if there was a quick way to extract all of the text out of the comments, and put them in the first available cell to the right. And if the old comments could be deleted as part of the extraction, that would be a bonus.

To which I said, of course that can be done!

And so, I wrote this little routine as a way of pulling the text from comments, placing it in the first available cell to the right, and blowing away the old comments if desired. Enjoy!

Sub ExtractComments()
' Description:  This iterates through all of the used cells in a worksheet
'               and where comments exist, extracts them and places them
'               in the next free cell to the right in the row where the
'               comment exists.
' Author:       Scott Lyerly
' Contact:      scott.c.lyerly@gmail.com
' Notes:        None.
' Arguments:    None
' Returns:      None
' Change Log:
' Name:                 Date:           Init:   Modification:
' ExtractComments V1    10-APR-2015     SCL     Original development
On Error GoTo ErrorHandler

' Variable declarations.
Dim sh          As Worksheet
Dim rng         As Range
Dim rCell       As Range
Dim rComments   As Range
Dim lAns        As Long
Dim lCalc       As Long

' Set some references to the active sheet and the sheet's used range.
Set sh = ActiveSheet
Set rng = sh.UsedRange

' Check to see if there are any comments in the sheet.
Set rComments = rng.SpecialCells(xlCellTypeComments)
If rComments Is Nothing Then GoTo Exit_Clean

' Ask user if they want to delete the comments as they are extracted.
lAns = MsgBox("Do you want to delete the comments as they are extracted?", vbYesNo + vbQuestion)

' Speed up the processing time.
With Application
lCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

' Iterate through the used range and where there are comments,
' extract their text and add it to the row.
For Each rCell In rng
If Not Intersect(rCell, rComments) Is Nothing Then
sh.Cells(rCell.Row, sh.Range("XFD" & rCell.Row).End(xlToLeft).Column + 1).Value = rCell.Comment.Text
' If the user selected YES above, delete the comments.
If lAns = vbYes Then rCell.Comment.Delete
End If
Next rCell


' Restore the environment.
With Application
.Calculation = lCalc
.ScreenUpdating = True
End With

' Delete objects to memory.
Set sh = Nothing
Set rng = Nothing
Set rComments = Nothing

Exit Sub

MsgBox Err.Number & ": " & Err.Description
Resume Exit_Clean

End Sub

Excel Geeking: Using Special Characters in ListObject Column Names in VBA

This is a short one, but a REALLY specific one.

Recently (i.e., today) I was tinkering with some List Objects, a.k.a. “tables” in VBA. All I was trying to do was to clear the contents of a column. Easy enough. It looked something like this:

Sheet1.Range("Table1[# Resource Req'd]").ClearContents

No Excel love. It kept throwing me an error. Specifically, error 1004, “Method ‘Range of object ‘_worksheet’ failed”.

What could I be doing wrong?

I started with the two best posts on Excel tables. First the Spreadsheet Guru’s post giving an introduction to tables. I couldn’t quite find what I was looking for, so I jumped over to Jon Peltier’s post on tables. It’s a little more detail than the overview by the Spreadsheet Guru. Unfortunately I couldn’t find what I needed there either, but it did give me an idea as to the problem.

You see, I have two “special” characters in the column header. Specifically, the hastag (#) and the apostrophe (‘). I figured my error had something to do with these, but neither post, nor a cursory Google search turned anything up when using them in VBA.

So I went the old school route. I recorded a macro wherein I highlighted the column in the table and then used Right-Click > Clear Contents.

What I found was that the code added an extra apostrophe before each special character. I’ve never seen this or needed to use it in any other code I’ve written for Excel, so maybe it’s specific to List Objects only. Then again, maybe not. Truth be told, I wasn’t interested in the history behind the extra apostrophe, I just needed the damn thing to work.

So now the code looks like this:

Sheet1.Range("Table1['# Resource Req''d]").ClearContents

And it works beautifully.

Excel Geeking: Extracting Outlook Address Book Information To Excel Using VBA

Yeah, I know there are a lot of posts out there about this already. So here’s another.

Truth be told, when I first went searching for items on this topic, I couldn’t find what I was looking for. Granted, it’s not outside the realm of possibility that I simply suck when it comes to a decent Google search. But even when my word choice did happen to land me luckily on a site with information on this topic, I still never really found what I was looking for.

What I really needed was a way to extract some particular columns of data from the Outlook address book. I was looking for Job Title specifically, and Alias if I could get at it. After poking around the web for a bit, and finding some snippets of code here or there, I finally wised up and decided to just follow the Outlook object model.

I love a good object model. Knowing the parent/child relationships from one object to another makes navigating through the application a cinch. For me, the thing I like most about knowing those parent/child relationships, and knowing what the various collections of objects are, is that it lets me iterate through all of the items in those collections.

That’s what I ended up doing with my routine. Once I found the right object that held the properties I was looking for, it was pretty easy to walk back up the model and make sure that I had all the necessary objects in the code.

In this case, because my company is on the Exchange Server, the Outlook object I needed was the ExchangeUser. It has all of the properties I would need, and then some. In addition to what I pulled, you can get address, phone numbers, manager, all kinds of good things. From there I just needed to work backward to figure out what collection did ExchangeUser belong to, and then back up from there. Once that’s been figured out, a simple For..Next loop does the trick.

The only other nuance here is that I need to run this from Excel, so that I could write the data to a worksheet from which I could slice and dice. No problem. Microsoft includes the ability to run other application object models from Excel. All you need to do is to set a reference to the MS Outlook Object Library, and then the Outlook object model is available to you in Excel. The rest is history.

The code for extracting the data is below. The list of properties the ExchangeUser offers can be found here. Enjoy.

Public Sub GetOutlookExchangeUserInformation()
'   This goes into the Global Address List for the MS Exchange Server
'   and returns a selection of data to a worksheet.
'   To get a view off all the potential data poitns that Outlook contains,
'   see the following URL:
'   https://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook.exchangeuser_properties.aspx

    ' Variable declarations.
    Dim olApp           As Outlook.Application
    Dim olNameSpace     As Namespace
    Dim olAddrList      As AddressList
    Dim olAddrEntry     As AddressEntry
    Dim olExchgnUser    As ExchangeUser
    Dim sh              As Worksheet
    Dim lCnt            As Long

    ' Set the Outlook object variables
    Set olApp = CreateObject("Outlook.Application")
    Set olNameSpace = olApp.GetNamespace("MAPI")
    Set olAddrList = olNameSpace.AddressLists("Global Address List")

    ' Create a new worksheet.
    Set sh = ThisWorkbook.Worksheets.Add

    ' Add some headers for our data.
    With sh
        .Cells(1, 1) = "NAME"
        .Cells(1, 2) = "FIRST NAME"
        .Cells(1, 3) = "LAST NAME"
        .Cells(1, 4) = "ALIAS"
        .Cells(1, 5) = "JOB TITLE"
        .Cells(1, 6) = "DEPARTMENT"
    End With

    ' Start the counter in the second row.
    lCnt = 2

    ' Iterate through the address entires in the address list.
    For Each olAddrEntry In olAddrList.AddressEntries

        ' For each entry, set the an exchange user object.
        ' I'm using t exchange user object because I find that
        ' most companies using Outlook are set up on MS Exchange Server.
        ' You can also use the GetContact whcih will return the
        ' the same information from the Outlook Contact Address Book
        ' (as opposed to the Global Address List).
        Set olExchgnUser = olAddrEntry.GetExchangeUser

        ' Turn off error handling, because occasionally you hit a
        ' record with nothing in it and it throws an error.
        On Error Resume Next

        ' Write the Outlook data to the worksheet.
        With olExchgnUser
            sh.Cells(lCnt, 1) = .Name
            sh.Cells(lCnt, 2) = .FirstName
            sh.Cells(lCnt, 3) = .LastName
            sh.Cells(lCnt, 4) = .Alias
            sh.Cells(lCnt, 5) = .JobTitle
            sh.Cells(lCnt, 6) = .Department
        End With

        ' Because an exchange server could have tens of thousands of entries,
        ' which will cause this routine to run for several minutes,
        ' update the status bar to let the user know that we are in fact
        ' still running and not hung up.
        Application.StatusBar = "Processing record " & lCnt & "..."

        ' If we did not hit an error, increment the counter.
        ' (If we did hit an error, we skip this because if we do not,
        ' we'll get a blank row in the middle of the data set.)
        If Err.Number = 0 Then lCnt = lCnt + 1
        ' Clear any error.
        ' Reset the error handling.
        On Error GoTo 0

    Next olAddrEntry

    ' Clear the status bar.
    Application.StatusBar = ""

    ' Prompt the user that we've finished.
    MsgBox "Extract done."

End Sub

Excel Geeking: “That Command Cannot Be Used On Multiple Selections” Error On A Single Cell

UPDATE (18-FEB-2015):

IMG_2940Remember this? Yeah, unfortunately I do too.

I wish I could say that my update from September was the end of the conversation. Sadly, it wasn’t. I started getting this error again and there was no random PowerPivot data connection in my workbook.

When I saw it pop up again, I was absolutely flumoxed. I had no idea what could be causing it, so I opened up a ticket with Microsoft again.

It took Microsoft three months and a lot of digging (I had to run all kinds of diagnostics on my machine, something that the security folks at my company were most unhappy about). I was beginning to think they would never uncover the issue until, Lo! they contacted me back and said they had found the cause.

And it’s a beauty.

It turns out that if you, using VBA, activate a sheet that’s hidden, save the file, then close it, when you reopen the file and try to copy and paste out of the file, the error occurs.

Whacky, right?

Don’t believe me? Try it yourself.

Create a macro-enabled workbook, save it as some name (doesn’t matter what), create a new module, and then paste this snippet into it:

Sub TestError()
Sheet1.Visible = xlSheetHidden
End Sub

In the ThisWorkbook module, paste in this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

Now close the file.

Now open the file. You might actually note that the sheet that was hidden is unhidden again. I think this is because it is the ActiveSheet, based on how the code set it and saved it when we closed.

Copy some cells in any sheet in the workbook. Try to paste them into a new workbook.

BAM! Error message.

What’s more interesting is that if you set the sheet in question to xlSheetVeryHidden, the error does not appear. It only works (or doesn’t, as the case may be) with xlSheetHidden.

So what it boils down to is that I am a victim of my own sloppy programming.

And with that, I will (hopefully) finally close the book on this error.

UPDATE (29-SEP-2014):

I promised that if I received an answer from Microsoft as to what bug could cause this issue, I would post it. And while I can’t say that Microsoft was able to tell me why the bug occurred, they were able to tell me what caused it. So here’s an update on where this stands.

powerpivotconnectionApparently, I had an errant data connection in the workbook. A data connection to, of all things, PowerPivot.

I have PowerPivot on my machine, but I’ve never really used it. I’ve played a little bit here and there, but I haven’t dug into it to understand the nitty gritty details. Well, at some point, I must have been playing with PowerPivot in this workbook, because a data connection was created.

Unfortunately, the data connection became “bad” at some point, meaning it didn’t point to anything. If you click the “Click Here” line in the Data Connection dialog box, it burps at you.powerpivotconnection_oops

Through the simple act of removing this data connection, I was able to clear the error and begin to copy/paste normally out of this workbook.

As I said, Microsoft was able to explain what caused the error (they pointed me to the data connection), but they were unable to explain why a bad connection would throw such an unusual error. I didn’t push it. The fact that they found the cause and that I could clear it on my end was a huge help. I also don’t know if it’s just a bad PowerPivot data connection that would cause this, or if any bad data connection will cause this. I hope not to ever find out.


“If I went back to work I would want a job like yours. I love Excel.”

This is a direct quote from a friend of mine on Facebook. It was in response to my post on controlling template releases. And sometimes I agree. The ability to play, arms deep, in Excel everyday is sometimes fun.

And sometimes it sucks it hard.

It’s been a less than banner week this week. I had a major update to an Excel-based application that I tried to roll out, only to have it fail spectacularly. I struggled with this thing for approximately two days before finally saying “F*ck It”, and reverting back to an older more stable version.

Here’s what happened:

I redesigned a planning template so that there would be a little more real estate on the UI worksheet. But I still needed a consolidated table of all the data. So, as part of the submission process that saves each template to a network location, I created a simple routine to copy/paste all of the relevant data to a worksheet I called “Export”.

As part of our consolidation process, I iterate through all the XLSM files in the network location, open them one by one, and take the data from the “Export” tab, paste it into a single temporary workbook, then copy/paste that into the Big Mutha.

(I know this sounds Draconian–cause it is. MS Access is not supported architecture, so I can’t feed it to a database.)

This is the same process this application has used for four years. The only difference is that this new change copies data from the “Export” tab as opposed to the UI worksheet. Easy-peasy, right?


After releasing the new template into the wild, this roll up process began to throw an error. But not a reasonable error. An error completely out of context for the operation I was performing

Specifically, I got this error:


You would think based on this error that I goofed in the coding of my copy/paste routines, trying to grab non-contiguous cells. That’s what this error looks like to me. But no, I was not. I got this error if I copied a simple range of cells, one column x number of rows. I got this error if I copied. One. Single. Cell.

I was pissed.

I exhausted Google over the last two days. I mined every frickin Excel forum I could find. And while I did see a couple of forum posts from people who were having the same issue, there were no replies in the thread. Cause what exactly are you supposed to do when you copy one cell and Excel thinks you’ve copied bunches of cells all over the place?

In my travels on Excel forums, I found one solution that worked half the time. If you close the file and then, instead of Opening the file you choose instead to Open and Repair, Excel will open it, attempt to repair it, tell you that it did some work, and say [Repaired] in the file name at the top of the application. The next step is to Save As the file. Same name, different name, up to you. But I found that this fix did not work unless you saved it, closed it, and reopened it. And even then, it only worked half the time. I tried coding this action, which you can do using xlRepair as the value for the CorruptLoad property in the Workbook.Open method, but it did not work uniformly. And since there were over two hundred files to apply this to, the manual effort involved was simply too much to manage.

At this point, unable to fix the issue, we rolled back the update to the previous version, cause we knew it worked. Because this error is completely out of context for the action, I’m assuming there’s a bug in Excel. There is a lot of code in these templates, a lot of which I wrote. Some type of action my code is taking must be causing a file corruption and thus this error. Therefore, come Monday morning, I’ll be giving Microsoft a call and reporting an issue.

Stay tuned. When/if I get an answer, I’ll post it.

Excel Geeking: Unwinding a Crosstabbed Dataset

Sometimes you have to work with a data set that is not in a state conducive to, well, anything. I’m thinking specifically about crosstabbed data sets. More than once I’ve had to deal with a data set that looks like this:


Oh, if only there were a way to unwind this file so that it was in a more normalized state for querying, lookups, and general analysis.

Well, now there is.

I created this routine a number of years ago because I had a monthly process that required taking a dataset like the one above and reformatting it into a layout that looks like this:


Needless to say, the data I was dealing with was a good deal larger than the sample above. I’ve used this routine on datasets that ended up outputting half a million rows. Yeah, it takes a few minutes, but it works. On smaller datasets, it works pretty fast.

Sub UnWindCrosstabData()
'   This takes a cross tabbed data and "unwinds" it to a flat file.

    ' Variable declarations.
    Dim rng         As Range
    Dim shNew       As Worksheet
    Dim lCols       As Long
    Dim lRows       As Long
    Dim i           As Long
    Dim j           As Long
    Dim r           As Long
    Dim vTemp       As Variant

    ' If the selection count is greater than one,
    ' assume a range to uwind has been selected.
    If Selection.Count > 1 Then
        Set rng = Selection
        ' Otherwise, use a range input box to get the range via the user.
        On Error Resume Next
        Set rng = Application.InputBox("Select the range to be unwound:", , , , , , , 8)
        ' If we get an error, the user did not select a range, and we exit the sub.
        If Err.Number <> 0 Then
            MsgBox "You need to select a range to use this utility.", vbExclamation, "Selection Error"
            Exit Sub
        End If
        On Error GoTo 0
    End If

    ' Get the total number of columns we will be dealing with.
    lCols = rng.Columns.Count

    ' Get the total number of rows we'll be dealing with, taking the use
    ' of headers into account.
    lRows = rng.Rows.Count - 1

    ' Speed up processing by shutting off screen flicker.
    Application.ScreenUpdating = False

    ' Set the new worksheet that will house the data.
    Set shNew = ActiveWorkbook.Worksheets.Add

    ' Reactivate the source sheet.

    ' Select and copy the source range, and paste it into the new sheet.
    ActiveSheet.Cells(1, 1).PasteSpecial xlPasteValues

    ' Reset the range to the pasted selection.
    Set rng = Selection

    ' Insert a column for the data header.
    rng.Cells(1, 2).Select

    ' Copy the record identifiers down, taking headers into account.
    For i = 1 To lCols - 2
        rng.Cells(2, 1).Select
        Range(Selection, Selection.Offset(lRows - 1, 0)).Select
        rng.Cells(1, 1).Select
        Selection.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False

    Next i

    ' Fill in the data header column.
    rng.Cells(1, 2).Value = "Data Header"
    r = 2
    For i = 1 To lCols - 1
        vTemp = rng.Cells(1, i + 2).Value
        For j = 1 To lRows
            rng.Cells(r, 2).Value = vTemp
            r = r + 1
        Next j
    Next i

    ' Insert a column for the data.
    rng.Cells(1, 3).EntireColumn.Select
    Selection.Insert Shift:=xlToRight

    ' Fill in the data.
    rng.Cells(1, 3).Value = "Data"
    r = 2
    For i = 0 To lCols - 2
        rng.Cells(r, i + 4).Select
        Range(Selection, Selection.Offset(lRows - 1, 0)).Select
        rng.Cells(r + (i * lRows), 3).Select
        Selection.PasteSpecial Paste:=xlPasteValues
    Next i

    ' Delete the unneeded columns
    rng.Cells(1, 4).EntireColumn.Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlToLeft

    ' Turn screen updating back on.
    Application.ScreenUpdating = True

End Sub

And having posted this, I’m already posting a hack. This routine only deals with one column of data to the left of your values. But what if you have more than one column? What if, for example, in the dataset above, you have name and address and city and state and zip code and email and phone number and…well, you get the idea.

What I do is to concatenate all of these fields together into a single column. I always use a delimiter, and for me, two good delimiters are the pipe (“|”) and the tilde (“~”). Once the field is concatenated and converted to values, I’ll run the routine above, then I’ll use Text To Columns to break all the columns back out again.

Use the above freely* and enjoy. If you have suggestion or find bugs, post in the comments section.


*and at your own risk, I assume no responsibility, legalese legalese legalese