Split large excel file by number of rows

Question

I have a large excel file with about 3000 rows.

I would like to split this data into groups of 100 rows

Is there a command in excel that can help me split this data into different

sheets or files for every 100th row?

Answer 1

Nice solution in this answer to another question on similar problem: https://stackoverflow.com/a/18001183

However, I needed to split the file without adding headers to each new file, just the data rows. My modified / simplified code:

Sub Test()
  Dim wb As Workbook
  Dim ThisSheet As Worksheet
  Dim NumOfColumns As Integer
  Dim RangeToCopy As Range
  Dim WorkbookCounter As Integer
  Dim RowsInFile
  Dim Prefix As String

  Application.ScreenUpdating = False

  'Initialize data
  Set ThisSheet = ThisWorkbook.ActiveSheet
  NumOfColumns = ThisSheet.UsedRange.Columns.Count
  WorkbookCounter = 1
  RowsInFile = 100                   'how many rows (incl. header) in new files?
  Prefix = "test"                    'prefix of the file name

  For p = 1 To ThisSheet.UsedRange.Rows.Count Step RowsInFile
    Set wb = Workbooks.Add

    'Paste the chunk of rows for this file
    Set RangeToCopy = ThisSheet.Range(ThisSheet.Cells(p, 1), ThisSheet.Cells(p + RowsInFile - 1, NumOfColumns))
    RangeToCopy.Copy wb.Sheets(1).Range("A1")

    'Save the new workbook, and close it
    wb.SaveAs ThisWorkbook.Path & "\" & Prefix & "_" & WorkbookCounter
    wb.Close

    'Increment file counter
    WorkbookCounter = WorkbookCounter + 1
  Next p

  Application.ScreenUpdating = True
  Set wb = Nothing
End Sub

Answer 2

You can do it this way with a helper column:

1st -- insert a column at the left (i.e column A).

2nd -- fill down the new column in a series from 1 to 3000 (or however many rows you have).

3rd -- in the two cells below the end of the series of column A, put in 100.5 and 200.5. Highlight these cells, then use Ctrl + Shift + Down Arrow and choose Fill Series (leaving the Step Value at 100)

4th -- Ascending Sort Column A.

5th -- Delete Column A. You'll have a blank row every 100 rows, with whatever else left over.

Answer 3

I found this VBA-based solution: http://nationbuilder.com/how_can_i_split_files_too_big_to_import

However, that solution lacks the ability decide how many rows every file has. Rather it is splitting the file in to X number of files with equal amount of rows. Of course you can just calculate this by dividing the total number of rows with the number of rows you want to have in each file but that rarely gives you exactly the number of rows you want due to rounding.

Maybe someone could come up with a modified macro?