Looping through excel cells forces to have at least 2 active cells

33 Views Asked by At

I am trying to print the content of the cells into a text file in a specific format. All works fine unless I have at least 2 Rows filled in. If I have only 1, it crashes. Could anyone help me, please? Thank you very much! Code below:

Sub exportBanners()

    Dim FileName As String
    FileName = ThisWorkbook.Path & "\MT_BANNERS_" & Format(Now, "ddmmyy-hhmmss") & ".txt"

    Dim c As Range, r As Range
    Dim output As String

    Dim lRow As Long
    Dim lCol As Long

    lRow = Cells(Rows.Count, 1).End(xlUp).Row

        For Each r In Range("A9:B9", Range("A9:B9").End(xlDown)).Rows

            For Each c In r.Cells

                 output = output & "|" & c.Value

            Next c
            output = output & vbNewLine

        Next r

    Open FileName For Output As #1

    Print #1, output

    Close

    MsgBox "MT BANNERS requested successfully!"

End Sub
0

There are 0 best solutions below