2 responses to “How to Loop through Your Excel Worksheet with VBA”

  1. There is also no need to select anything. It is not good VBA programming practice to do so. That is inefficient and may cause the screen to jump around. Also, there is no reason for the variables to have scope outside the subroutine. Better coding would be:

    Sub Main()

    Dim lng As Long
    Dim r As Range
    Dim strEmail As String
    Dim strAll As String

    With ThisWorkbook.Sheets(“Sheet1”)
    strAll = “”
    Set r = .Range(“A1”).CurrentRegion
    For lng = 2 To r.Rows.Count Step 1
    strEmail = r.Rows(lng).Cells(1)
    If Right(strEmail, 2) “fr” Then ‘exclude France
    strAll = strAll & strEmail & “;”
    End If
    Next lng
    .Range(“C2”) = strAll
    Set r = Nothing
    End With

    End Sub

  2. This is very bad code. Among other issues, you have an actual logic error. The lines

    Range(“C2”) = strAll
    Sheets(“Sheet1”).Select

    should be reversed in order.