Need help modifying a VBA code to add an exception

[spittingfire]

Hi All,

I have the following Excel VBA 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.

[SamT]

Not tested:

      For Row = 1 To (LastRow - LastRow Mod 50) 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
        
        newCSV.Worksheets(1).UsedRange.ClearContents
        .Rows(Rows.Count - (LastRow Mod 50) & ":" & LastRow).EntireRow.Copy newCSV.Worksheets(1).Range("A1")
        newCSV.SaveAs Filename:=Replace(inputWb.FullName, ".xlsb", "(" & n + 1 & ").csv"), FileFormat:=xlCSV, CreateBackup:=False

You might want to chek for at least 50 rows before the loop. Just in case.

[spittingfire]

Thanks for the SamT but that did not work for me. I currently tested it with 220 rows with the VBA correctly creating the first 4 files with 50 rows each. For the 5th file I was expecting to see 20 rows instead I got 1 row of data and 1048337 rows of commas

Sample below

00,8936480,LATF,08/20/2015,08/20/2015,12:00,00:24,Coded By NCC
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,

[SamT]

.Rows(LastRow - (LastRow Mod 50) & ":" & LastRow)

[spittingfire]

Thanks again for taking the time to help me out.

I got some help and was able to come up with a modification that worked

Sub Split_50()

    Dim inputWb As Workbook
    Dim LastRow As Long, iRow 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
                
        For iRow = 1 To LastRow Step 50
            newCSV.Worksheets(1).Range("1:50").Delete
            n = n + 1
             .Rows(iRow).Resize(Application.WorksheetFunction.Min(50, LastRow - iRow  + 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

Thanks again for your help