title: Empty rows in file generated from macros in excel
body: <p>I am trying to split a file with 120 records into files of at-most 50 records each. So expectation is it should genarate 2 files with 50 records and 1 file wit 20 but what I am getting is 3 files of 51 records with 1 empty file in the end for first 2 and 31 empty lines in 3rd file.</p>
  <pre><code>Sub SplitAndSaveFile()
  Dim myRow As Long, myBook As Workbook, splitCount As Integer, thisWBName As String, splitCountStr As String, spaceRange As Range
  lastRow = ThisWorkbook.Sheets(&quot;Data&quot;).Cells(rows.Count, 1).End(xlUp).Row
  splitCount = 1
  splitCountStr = CStr(splitCount)
  thisWBName = Replace(ThisWorkbook.Name, &quot;.xlsm&quot;, &quot;&quot;) + &quot;_Part&quot;
  For myRow = 4 To lastRow Step 50
      Set myBook = Workbooks.Add
      ThisWorkbook.Sheets(&quot;Data&quot;).rows(myRow &amp; &quot;:&quot; &amp; myRow + 49).EntireRow.Copy myBook.Sheets(&quot;Sheet1&quot;).Range(&quot;A1&quot;)
      myBook.SaveAs (ThisWorkbook.Path + &quot;\&quot; + thisWBName + splitCountStr + &quot;.txt&quot;), FileFormat:=xlText
      myBook.Close
      splitCount = splitCount + 1
      splitCountStr = CStr(splitCount)
  Next myRow
  MsgBox (&quot;File(s) generated.&quot;)
  End Sub
  </code></pre>