How to Loop through Your Excel Worksheet with VBA

It is useful sometimes to read a worksheet line-by-line and selectively take data from it. An example below shows how to do this.

The article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.

The following example manipulates the data by examining each row of email addresses and concatenating them into a single address line. While Excel has built-in Transpose functions, we intend with our code to exclude some specific addressees and add delimiters.

Our application needs one sheet only. Copy the information below to cell A1:

Place a button on the form to kick-off the code.

Let’s assume there are hundreds of these emails instead of only five; we want to send each the same message and the same copy of our screenplay. There are several ways of doing this, most notably by calling the emailer, like Outlook, from VBA code. However, this exercise serves two purposes:

• Show how to cycle easily through a worksheet;
• Highlight Excel’s ability to examine each row, and act on it.

The VBA Code

The VBA code will concatenate all the addresses, inserting a semi-colon after each, so that they can be pasted in one go into the email’s addressee field. You will note from the code that, after examining the rows, it will exclude French ones.

Insert a module, and enter the following:

Option Explicit
Dim strEmail As String
Dim strAll As String

Sub Main()
    strAll = ""
    Range("C2") = strAll     'clear previous program results
    Sheets("Sheet1").Select
    Range("A2").Select
    Do While ActiveCell > ""      'loop until row 7
        strEmail = ActiveCell
        If Right(strEmail, 2) <> "fr" Then      'exclude France
            strAll = strAll & strEmail & ";"
        End If
        Range("A" & ActiveCell.Row + 1).Select     'move on a row in the SAME column
    Loop
    Range("C2") = strAll
End Sub

The code keeps the active cell in column A, only the rows move on. To reference information elsewhere in a row, we would use:

Variable = Activecell.offset(offset rows, offset columns)

To get a value from column B in the active row, for instance, the code would read Activecell.offset(0, 1). To get A2’s value when we’re in A3 would read Activecell.offset(-1, 0).

To test your code, assign the button to the macro “Main”.

Some Internet Service Providers, as an anti-spam precaution, might limit the number of emails you can send at once. If you use this method, it is probably best to check with them before despatching hundreds of emails in one go.

Handling Excel File Corruption

From time to time we encounter the corruption of workbooks to the extent that Excel itself can’t recover them. This is a disaster for most Excel users, including the experts. However, if you have an effective recovery tool to hand, you can restore damaged xlsx files easily and quickly.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair rar corruption and sql recovery software products. For more information visit www.datanumen.com