How can I optimize this vba loop code?

Hi I wrote this code. but this code is very slow. How can I optimize this code?

Private Sub printItem(r, lastCol, objStream)
    FirstCol = 1

    Dim strFirst As String

    strFirst = CStr(ActiveSheet.Cells(r, 1).Value)

    If strFirst = "" Then
        Exit Sub
    End If

        objStream.WriteText "    <"
        objStream.WriteText "TagName"
        objStream.WriteText " "

        For c = FirstCol To lastCol

            data = CStr开发者_JAVA百科(ActiveSheet.Cells(r, c).Value)

            If LenB(Trim$(data)) > 0 Then
                objStream.WriteText g_AttributeName(c)
                objStream.WriteText "="""
                objStream.WriteText data
                objStream.WriteText """ "
            End If

        Next c

        objStream.WriteText "/>"

    objStream.WriteText vbNewLine
End Sub

---

This is the likely reason why your code is slow: you are looping through cells. There is significant overhead to each communication between VBA and Excel sheet data, and this adds up when you refer to many cells one at a time.

Instead, you should load all your data at once into a Variant array, and then loop through that array, as shown below. This is significantly faster.

    Dim varData As Variant

    varData = ActiveSheet.Cells(r, FirstCol).Resize(1, lastCol - FirstCol + 1)

    For c = LBound(varData, 2) To UBound(varData, 2)

        data = CStr(varData(1, c))

        If LenB(Trim$(data)) > 0 Then
            ' etc.
        EndIf

    Next c

For further reading, have a look at this old but still relevant article: http://www.avdf.com/apr98/art_ot003.html