Deleting All Empty Rows

Category: General VBA | [Item URL]

The subroutine below is a fast and efficient way to delete all empty rows in the active worksheet.

Sub DeleteEmptyRows()
    LastRow = ActiveSheet.UsedRange.Row - 1 + _
    Application.ScreenUpdating = False
    For r = LastRow To 1 Step -1
        If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
    Next r
End Sub

Notice that loop uses a negative Step value, which causes the rows to be deleted from the bottom to the top.

Working With Names In VBA

Category: General VBA | [Item URL]

Most Excel users realize the value of using names in the workbook. This tip describes some VBA techniques that are useful when working with names.

Listing all names

You probably know that you can get a list of all names by using the Insert Name Paste command, and then clicking the Paste List button. The list that gets generated omits hidden names and sheet level names. The subroutine below creates a list of all names and their references, beginning in cell A1.

Sub ListAllNames()
    Row = 1
    For Each n In ActiveWorkbook.Names
        Cells(Row, 1) = n.Name
        Cells(Row, 2) = " " & n.RefersTo
        Row = Row + 1
    Next n
End Sub

This subroutine works by looping through the Names collection.

Hidden names

Hidden names are names that are not visibile to the end user. Developers often use hidden names to store various types of information. To demonstrate, just save a range in HTML format using the Internet Assistant Wizard add-in (use the File Save as HTML command). After doing so, execute the ListAllnames subroutine and you'll find that your workbook contains more than a dozen new hidden names that contain the parameters you specified in the Internet Assistant Wizard. The Internet Assistant Wizard uses this information as default values the next time it is run.

A hidden name has its visible property set to False. The only way change this propery is with VBA. For example, the statement below makes the name MyRange a hidden name:

ActiveWorkbook.Names("MyRange").Visible = False

Sheet level names

A sheet level name is a name that is valid for a particular worksheet. To create a sheet level name, precede the name with the worksheet name and an exclamation point.

To find out if a particular name is a sheet level name, simply determine if the name's Name property contains an exclamation point. You can use the Like operator to do this. The expression below is True if MyRange is a sheet level name.

ActiveWorkbook.Names("MyRange").Name  Like "*!*"

Linked names

A name can also refer to a different workbook. I call this type of name a linked name. To find out if a particular name is a linked name, simply determine if the name's RefersTo property contains a right bracket. The expression below is True if MyRange is a linked name.

ActiveWorkbook.Names("MyRange").RefersTo  Like "*[[]*"

NOTE: A bracket is a special character when using the Like command, so it must be enclosed in brackets.

Erroneous names

It's not uncommon for a name to refer to a range that no longer exist (deleting a sheet can cause this). These types of names are responsible for the "phantom link" phenomenon in which Excel asks you to update links when you open a workbook -- even if no links really exist.

To find out if a particular name is an erroneous name, simply determine if the name's RefersTo property conains "REF!". The expression below is True if MyRange is an erroneous name.

ActiveWorkbook.Names("MyRange").RefersTo Like "*REF!*" 

Using Auto List Members In The VB Editor

Category: General VBA | [Item URL]

One of the most useful features in VBA is an option called Auto List Members. When this option is in effect, VBA displays a list that contains information that would logically complete the statement at the current insertion point.

For example, when you type Application (followed by a dot), you'll get a drop-down list of all of the properties and methods for the Application object. Simply select from the list and be on your way. This feature is useful because it:

  • Reduces typing
  • Prompts you with properties and methods you may not even know about
  • Eliminates spelling errors

If you're working in a code module for a UserForm, you may need to specify an object located on the UserForm (such as a ListBox) -- but you can't remember the object's name. The solution? Type Me followed by a dot. You'll get a list of all of the properties, methods, and objects for the UserForm. Then you can simply select the object's name from the list.

Similarly, you can type VBA followed by a dot and you'll get a list of VBA statements, functions, and constants. The figure below demonstrates.

NOTE: if Auto List Members isn't working for you, select Tools Options to view the Options dialog box. Click the Edit tab and make sure you have the Auto List Members option checked. (5786 bytes)

Automatically Resetting The Last Cell

Category: General VBA | [Item URL]

It's commonly known that Excel sometimes has a problem in keeping track of the "last cell" in a worksheet. The last cell is lower right cell that contains data.

Any VBA reference to the UsedRange.Rows.Count property will force Excel to recognize the updated last cell on a worksheet. Therefore, if you're using Excel 97 you can insert the following subroutine into the code module for the ThisWorkbook object:

Private Sub Workbook_SheetSelectionChange _
  (ByVal Sh As Object, ByVal Target As Excel.Range)
  x = Sh.UsedRange.Rows.Count
End Sub

This subroutine is executed whenever the selection is changed on a worksheet. It quickly accesses the Count property -- and the last cell is reset. Pressing End-Home will always take you to the real last cell in the worksheet.

CAUTION: This technique has one potentially serious side-effect. Executing this procedure wipes out Excel's undo stack. In other words, if you use this technique you won't be able to use Undo. Therefore, a better technique might be to reset the used range when the workbook is saved (saving a workbook also zaps the undo stack). Here's an example:

Private Sub Workbook_BeforeSave _
  (ByVal SaveAsUI As Boolean, Cancel As Boolean)
    For Each Sh In ThisWorkbook.Worksheets
        x = Sh.UsedRange.Rows.Count
    Next Sh
End Sub

Synchronizing Sheets In A Workbook

Category: General VBA | [Item URL]

If you use multisheet workbook, you probably know that Excel cannot "synchronize" the sheets in a workbook. In other words, there is no automatic way to force all sheets to have the same selected range and upper left cell.

The VBA macro listed below uses the active worksheet as a base, and then performs the following on all other worksheets in the workbook:

  • Selects the same range as the active sheet
  • Makes the upper left cell the same as the active sheet

Note: Hidden worksheet are ignored.

The SynchSheets Subroutine

Following is the listing for the subroutine:

Sub SynchSheets()
'   Duplicates the active sheet's active cell upperleft cell
'   Across all worksheets
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    Dim UserSheet As Worksheet, sht As Worksheet
    Dim TopRow As Long, LeftCol As Integer
    Dim UserSel As String
    Application.ScreenUpdating = False

'   Remember the current sheet
    Set UserSheet = ActiveSheet
'   Store info from the active sheet
    TopRow = ActiveWindow.ScrollRow
    LeftCol = ActiveWindow.ScrollColumn
    UserSel = ActiveWindow.RangeSelection.Address
'   Loop through the worksheets
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Visible Then 'skip hidden sheets
            ActiveWindow.ScrollRow = TopRow
            ActiveWindow.ScrollColumn = LeftCol
        End If
    Next sht
'   Restore the original position
    Application.ScreenUpdating = True
End Sub

Using the Subroutine

To use this subroutine, copy it to a VBA module (your Personal Macro Workbook is a good choice). Then, activate a worksheet and execute the SynchSheets subroutine. All of the worksheets will then have the same range selection and and upper left cell as the active sheet.

Handling The Workbook Beforeclose Event

Category: General VBA | [Item URL]

The BeforeClose event occurs before a workbook is closed. This event is often used in conjunction with a Workbook_Open event handler. For example, you can use the Workbook_Open procedure to initialize things, and use the Workbook_BeforeClose procedure to "clean up" things before the workbook is closed.

If you attempt to close a workbook that hasn't been saved, Excel displays a prompt asking if you want to save the workbook before closing. A potential problem can arise because by the time the users sees this message, the BeforeClose event has already occurred -- which means that your Workbook_BeforeClose subroutine has already executed.

Consider this scenario: You need to display a custom menu when a particular workbook is open. Therefore, your workbook uses a Workbook_Open procedure to create the menu when the workbook is opened, and it uses a Workbook_BeforeClose procedure to remove the menu when the workbook is closed. These two event-handler procedures (which must be in the code module for the ThisWorkbook object) are listed below.

Private Sub Workbook_Open()
    Call CreateMenu
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call DeleteMenu
End Sub

But, as I noted above, Excel's "save workbook before closing" prompt occurs after the Workbook_BeforeClose event-handler runs. So if the user clicks Cancel, the workbook remains open -- but the custom menu item has already been deleted!

One solution to this problem is to by-pass Excel's prompt and write your own code in the Workbook_BeforeClose procedure to ask the user to save the workbook. The code below demonstrates. This procedure must be in the code module for the ThisWorkbook object.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not Me.Saved Then
        Msg = "Do you want to save the changes you made to "
        Msg = Msg & Me.Name & "?"
        Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
        Select Case Ans
            Case vbYes
            Case vbNo
                Me.Saved = True
            Case vbCancel
                Cancel = True
                Exit Sub
          End Select
    End If
    Call DeleteMenu 'Change this to your own subroutine
End Sub

This procedure first determines if the workbook has been saved. If so, no problem -- the DeleteMenu subroutine is executed and the workbook is closed. But if the workbook has not been saved, the procedure displays a message box that duplicates the one Excel would normally show.

If the user clicks Yes the workbook is saved, the menu is deleted, and the workbook is closed. If the user click No, the code sets the Saved property of the Workbook object to True (but doesn't actually save the file), and deletes the menu. If the user clicks Cancel, the BeforeClose event is canceled and the subroutine ends without deleting the menu.

Pausing A Macro To Get A User-selected Range

Category: General VBA | [Item URL]

This tip describes how to pause a VBA macro so the use can select a range. The trick is to use the InputBox function of the Application object.

NOTE: Do not confuse this with VBA's InputBox function. Although these two functions have the same name, they are not the same.

The Sub procedure listed below demonstrates how to pause a macro and let the user select a cell.

Sub GetUserRange()
    Dim UserRange As Range   
    Output = 565
    Prompt = "Select a cell for the output."
    Title = "Select a cell"

'   Display the Input Box
    On Error Resume Next
    Set UserRange = Application.InputBox( _
        Prompt:=Prompt, _
        Title:=Title, _
        Default:=ActiveCell.Address, _
        Type:=8) 'Range selection

'   Was the Input Box canceled?
    If UserRange Is Nothing Then
        MsgBox "Canceled."
        UserRange.Range("A1") = Output
    End If
End Sub

The input box is shown below. (1825 bytes)

Specifying a Type argument of 8 is the key to this procedure. Also, note the use of On Error Resume Next. This statement ignores the error that occurs if the user clicks the Cancel button. If so, the UserRange object variable is not defined. This example displays a message box with the text Canceled. If the user clicks OK, the macro continues.

By the way, it's not necessary to check for a valid range selection. Excel takes care of this for you.

WARNING: Make sure ScreenUpdating is turned on. Otherwise, you won't be able to select a cell.

Sending Personalized Email From Excel

Category: General VBA | [Item URL]

If you use Microsoft Outlook, it's fairly easy to create a macro to send personalized email from Excel. Just create an Outlook object and manipulate Outlook's object model using data from your worksheet.

But what if you don't use Outlook? This tip presents a technique for sending personalized email from Excel. I developed and tested the code using Outlook Express (which does not support VBA). It may or may not work with other email clients.

An Example

Consider the simple worksheet shown in the figure below. The goal is to send a personalized email to each of the three people listed in the workbook, informing them of their annual bonus amount.

For example, the first email message would read:

Dear John Jones,

I am pleased to inform you that your annual bonus is $2,000.

William Rose

The VBA Code

The VBA code to create and send these email messages is listed below.

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
    Dim Email As String, Subj As String
    Dim Msg As String, URL As String
    Dim r As Integer, x As Double
    For r = 2 To 4 'data in rows 2-4
'       Get the email address
        Email = Cells(r, 2)
'       Message subject
        Subj = "Your Annual Bonus"

'       Compose the message
        Msg = ""
        Msg = Msg & "Dear " & Cells(r, 1) & "," & vbCrLf & vbCrLf
        Msg = Msg & "I am pleased to inform you that your annual bonus is "

        Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf
        Msg = Msg & "William Rose" & vbCrLf
        Msg = Msg & "President"
'       Replace spaces with %20 (hex)
        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
'       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")        
'       Create the URL
        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg        

'       Execute the URL (start the email client)
        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

'       Wait two seconds before sending keystrokes
        Application.Wait (Now + TimeValue("0:00:02"))
        Application.SendKeys "%s"
    Next r
End Sub

How it Works

The first part of the macro is straightforward. It loops through the rows, picks up the email address and composes the custom message. Notice the use of Excel's Substitute worksheet function. This is used to replace space characters and carriage return/line feed sequences with their hex code equivalent (the standard characters are not allowed in a URL). A URL is created, and stored in the variable named URL. It then uses the ShellExecute API function to execute associated application.

At this point, the compose window for Outlook Express is displayed and ready to be sent. It uses the SendKeys message to send an Alt+S command (the "send" command for Outlook Express). Notice that there is a two second delay before the keystroke is sent. This can be adjusted as needed (one second may work fine).

I only use SendKeys as a last resort -- and this qualifies. Because of the pause, this macro is not very fast.

Note: The size of the email message is limited to (I think) 255 characters.

Note: My first approach was to use the FollowHyperlink method of the Workbook object. However, I was not able to figure out how to insert blank lines in the email body. Ron de Bruin solved that problem (see below).


"Bugs" suggested a way to insert a block of static text into the message, thereby negating the 255-character limit. The solution? Copy your text to the Clipboard. Then add the following statement before the Application.SendKeys statement:

 Application.SendKeys "{Tab}{Tab}{Tab}{Tab}{Tab}^{End}{Return}{Return}^v"

This statement tabs through the email window headers, jumps to the end of the message text, inserts two carriage returns, and pastes the contents of the clipboard. You may need to experiment with the number of Tabs sent.

For more information on sending email from Excel, visit Ron de Bruin's site.

Clearing The Advanced Filter Dialog Box

Category: General VBA | [Item URL]

When you choose the Data, Filter, Advanced Filter command, Excel displays its Advanced Filter dialog box. You've probably noticed that Excel "remembers" the previous range specifications for the List range, Criteria range, and Copy to range. In most cases, this is useful. But if you're working with several different worksheet databases, you may prefer that Excel uses the current database rather than the previous database.

There is no direct way to clear the previous settings from the Advanced Filter dialog box. However, you can use the simple VBA procedure listed below.

Sub ShowAdvancedFilterDialog()
'   Delete names
    On Error Resume Next
    With ActiveWorkbook
    End With
    On Error GoTo 0
'   Display the dialog box
End Sub

How it works

Excel keeps track of the previous Advanced Filter range specifications by using three name: _FilterDatabase, Criteria, and Extract. The ShowAdvancedFilterDialog procedure simply deletes these names, and then uses the Show method to display the Advanced Filter dialog box. Because these names are not defined, it's as if you are using the Advanced Filter command for the first time.

Note: The _FilterDatabase name is a hidden name. Therefore, the only way to delete it is by using VBA code. The Criteria and the Extract names are normal names, and can be deleted using the standard Define Name dialog box.

Using the procedures

To use this procedure, copy it and paste it to a VBA module in your workbook. Then, execute the ShowAdvancedFilterDialog macro instead of the Data, Filter, Advanced Filter command.

Mail Merge - Without Word

Category: Printing / General VBA | [Item URL]

A companion file is available: Click here to download

Mail merge is the process of merging information from a database into a text document, and then printing the document. The result is a series of personalized documents.

Normally, Microsoft Word is used for this type of operation. Word can perform a mail merge with data stored in an Excel workbook. The process works well, but in some cases it may be preferable to eliminate Word and do all of the work with Excel.

The basics

Using Excel to perform a mail merge requires:

  • A range that contains the data to be merged. In the example, this is on the sheet named Data.
  • A range that contains the text. In the example, this is on the sheet named Form.
  • A cell that contains the row number of the "current record." In this example, that cell is C3.
  • Formulas that use Excel's INDIRECT function to retrieve data from the current record. These formulas are in column L.
  • Formulas within the text that refer to the cells in column L. The example uses several such formulas to personalize the letter.
  • A simple macro that loops through the data, incrementing the current record number and printing the sheet.

This example, of course, can be adapted to many other purposes.

This example has a few additional features:

  • The ability to specify the first and last records from the database
  • Buttons to assist in navigating through the workbook
  • An option to preview instead of print
  • A Help button that displays instructions

Page 5 of 17 pages
[Previous page]   [Next page]

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2018, J-Walk & Associates, Inc.
Privacy Policy