Need help modifying a VBA code to add an exception
Hi All,
I have the following Excel VBA Code
Code:
Sub Split_50()
Dim inputFile As String, inputWb As Workbook
Dim LastRow As Long, row As Long, n As Long
Dim newCSV As Workbook
Set inputWb = ActiveWorkbook
With inputWb.Worksheets("Export")
LastRow = .Cells(Rows.Count, "B").End(xlUp).row
Set newCSV = Workbooks.Add
n = 0
For row = 1 To LastRow Step 50
n = n + 1
.Rows(row & ":" & row + 50 - 1).EntireRow.Copy newCSV.Worksheets(1).Range("A1")
'Save in same folder as input workbook with ".xlsx" replaced by "(n).csv"
newCSV.SaveAs Filename:=Replace(inputWb.FullName, ".xlsb", "(" & n & ").csv"), FileFormat:=xlCSV, CreateBackup:=False
Next
End With
End Sub
The code works well and what it is doing is breaking a large file into smaller files with a max of 50 rows that is being saved into a CSV file.
The problem is when the last file being created is less than 50 rows it is adding spaces (commas in this case) to total 50 and this is causing errors when I try to upload into another system.
If possible what I will like to do is add an exception of sorts where if it is less than 50 then only save the exact number of remaining rows.
Much appreciated if you are able to assist me with this.
