엑셀(EXCEL) – 수십만행의 자료를 특정행만큼 분리해서 엑셀 워크북 만들기

제목처럼 수십만, 수백만행의 자료를 다룰려면 속도와 메모리때문에 힘든데 이것을 특정행만큼

잘라서 워크북을 만들어 주는 기능입니다.

Sub Split_File_By_Row()

Dim wrk As Workbook
Dim sht As Worksheet

Dim cols As Integer, dcnt As Integer, WorkbookCounter As Integer

Dim Rng2Cp As Range

Dim p As Long

Dim TempStr As String, strFullPath As String

strFullPath = FileNameNoExtensionFromPath(ThisWorkbook.FullName)

TempStr = Right(strFullPath, Len(strFullPath) – (InStrRev(strFullPath, “\”)))

dcnt = InputBox(“분할할 행 갯수를 입력하세요”)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set sht = ThisWorkbook.ActiveSheet

cols = sht.UsedRange.Columns.Count
WorkbookCounter = 1

Rows(1).Delete xlUp

For p = 1 To sht.UsedRange.Rows.Count Step dcnt

Set wrk = Workbooks.Add

Set Rng2Cp = sht.Range(sht.Cells(p, 1), sht.Cells(p + dcnt – 1, cols))
Rng2Cp.Copy wrk.Sheets(1).Range(“A1”)

wrk.SaveAs ThisWorkbook.Path & TempStr & “_” & WorkbookCounter
wrk.Close

WorkbookCounter = WorkbookCounter + 1

Set Rng2Cp = Nothing

Next p

Application.DisplayAlerts = True
Application.ScreenUpdating = True

Set wrk = Nothing

End Sub

Function FileNameNoExtensionFromPath(strFullPath As String) As String

Dim intStartLoc As Integer
Dim intEndLoc As Integer
Dim intLength As Integer

intStartLoc = Len(strFullPath) – (Len(strFullPath) – InStrRev(strFullPath, “\”) – 1)
intEndLoc = Len(strFullPath) – (Len(strFullPath) – InStrRev(strFullPath, “.”))
intLength = intEndLoc – intStartLoc

FileNameNoExtensionFromPath = Mid(strFullPath, intStartLoc, intLength)

End Function

첨부 화일 :