Hello,

I'm looking to split an Excel file into separate files by 10 incrementing number of lines. For example, I have a file with simple 500 lines/rows (all in A1 column) and need to split it to
chunk1-row1-10.csv (10 lines)
chunk2-row11-30.csv (20 lines)
chunk3-row31-60.csv (30 lines)
chunk4-row61-100.csv (40 lines)
chunk5-row101-150.csv (50 lines)
chunk6-row151-210.csv (60 lines)
chunk7-row211-280.csv (70 lines)
...etc

So the first chunk starts with 10, and the second increments that by 10 and so on.

I have found a Macro that split a large text file to chunks of 11 lines/rows for each file, but I don't know how to modify it to increment. Tried adding and changing many values to no avail.

Sub macro1()
Dim rLastCell As Range
Dim rCells As Range
Dim strName As String
Dim lLoop As Long, lCopy As Long
Dim wbNew As Workbook

With ThisWorkbook.Sheets(1)
Set rLastCell = .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)

For lLoop = 1 To rLastCell.Row Step 10
lCopy = lCopy + 1
Set wbNew = Workbooks.Add
.Range(.Cells(lLoop, 1), .Cells(lLoop + 10, .Columns.Count)).EntireRow.Copy _
Destination:=wbNew.Sheets(1).Range("A1")
wbNew.Close SaveChanges:=True, Filename:="Chunk" & lCopy & "Rows" & lLoop & "-" & lLoop + 10
Next lLoop
End With

End Sub
It will be a bonus if the chunk files could be save to txt format.
I'd appreciate if anyone can help me achieve this as it will save me hours of work.

Thanks,
Roger