PDA

View Full Version : [SOLVED] Need help modifying a VBA code to add an exception



spittingfire
08-21-2015, 01:47 PM
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
08-21-2015, 04:19 PM
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
08-21-2015, 04:43 PM
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
08-21-2015, 06:39 PM
.Rows(LastRow - (LastRow Mod 50) & ":" & LastRow)
http://www.vbaexpress.com/forum/images/smilies/banghead.gifhttp://www.vbaexpress.com/forum/images/smilies/banghead.gifhttp://www.vbaexpress.com/forum/images/smilies/banghead.gifhttp://www.vbaexpress.com/forum/images/smilies/banghead.gifhttp://www.vbaexpress.com/forum/images/smilies/banghead.gifhttp://www.vbaexpress.com/forum/images/smilies/banghead.gifhttp://www.vbaexpress.com/forum/images/smilies/banghead.gifhttp://www.vbaexpress.com/forum/images/smilies/banghead.gifhttp://www.vbaexpress.com/forum/images/smilies/banghead.gifhttp://www.vbaexpress.com/forum/images/smilies/banghead.gifhttp://www.vbaexpress.com/forum/images/smilies/banghead.gifhttp://www.vbaexpress.com/forum/images/smilies/banghead.gif

spittingfire
08-21-2015, 07:00 PM
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 :)