VBA: Exit For Loop when row is empty

excel, vba

Solution

This code will start in row 10 and run until it finds a blank cell in the second column. Note that I also shortened your code a bit (though it still does the same writing to a file):

Sub export_Test()
    Dim myRow As Long
    myRow = 10
    While Cells(myRow, 2).Value <> ""
        Open "C:\mallet\test\" & Cells(myRow, 1) & ".txt" For Append As #1
        Print #1, Cells(myRow, 2).Value
        Close #1
        myRow = myRow + 1
    Wend
End Sub

Problem

I'm using the following code to export rows to individual text files: ``` Sub export_Test() Dim firstRow As Integer, lastRow As Integer, fileName As String Dim myRow As Integer, myStr As String firstRow = 10 lastRow = 29 For myRow = firstRow To lastRow fileName = "C:\mallet\test\" & Cells(myRow, 1) & ".txt" Open fileName For Append As #1 myStr = Cells(myRow, 2).Value Print #1, myStr Close #1 Next End Sub ``` The problem is that this code is for a specific number of rows. I want to use this code for different data samples, so the number of rows in the excel file will vary and could number in the thousands. I need the lastRow variable to be set to an infinite number and exit the For Loop when it hits an empty row.

Original source

Related problems