VBA: Exit For Loop when row is empty

Question

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.

http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba — Siddharth Rout, Feb 20 '13 at 21:10
Also since you are using Rows, would recommend declaring them as `Long` instead of `integers` :) — Siddharth Rout, Feb 20 '13 at 21:13
@Sid on a 32 bit OS you should _always_ use `Long`, unless there is a specific need to use 16 bit numbers (`Integer`) — chris neilsen, Feb 21 '13 at 06:09

Peter Albert · Accepted Answer · 2013-02-22T14:26:36.770

4

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

edited Feb 22 '13 at 14:26

answered Feb 20 '13 at 21:20

Peter Albert

16,917
5
64
88

1

You may want to adjust this code per @SiddharthRout's comment above. – Onorio Catenacci Feb 22 '13 at 14:20
1

done. though i would not have minded if you had done it either... :-) – Peter Albert Feb 22 '13 at 14:27

cardmagik · Answer 2 · 2013-02-20T21:24:41.813

0

This is code from a project of mine that does exactly what you want - end with a blank value

Sub export_Test()

Dim firstRow As Integer, lastRow As Integer, fileName As String
Dim myRow As Integer,  myStr As String

   firstRow = 10 
   myRow = firstRow

   ' Seed initial value
   Cells(myRow, 1).Select

   ' Keep going until a blank cell is found
   While Trim(ActiveCell.Value) <> ""

      fileName = "C:\mallet\test\" & ActiveCell.Value & ".txt"

      Open fileName For Append As #1

      myStr = Cells(myRow, 2).Value
      Print #1, myStr
      Close #1 

      ' Get the next value      
      myRow = myRow + 1
      Cells(myRow, NameCol).Select
   Wend

End Sub

edited Feb 20 '13 at 21:24

answered Feb 20 '13 at 21:13

cardmagik

1,698
19
17

I'm still pretty new to vba. Would my For Loop go inside this code? – Momo Feb 20 '13 at 21:17
Here is your code revised with the new code. However, I didn't test this so you may have to tweak it some - it will be in the main answer in a moment – cardmagik Feb 20 '13 at 21:23

VBA: Exit For Loop when row is empty

2 Answers2