A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Hi @Payal Gupta
Yes, it can handle hundreds of thousands of rows. You're right that the slow part is checking and copying rows one at a time. The fix is to let Excel do the heavy lifting: read the column into memory once to get the unique values, then use AutoFilter and copy all visible rows for each value in a single step. Turning off screen updating and automatic calculation while it runs also helps a lot.
Here's a version that keeps your current behaviour (asks for the header, creates one sheet per value) and shows progress in the status bar:
Sub SplitByColumn()
Dim src As Worksheet, ws As Worksheet
Dim hdr As String, col As Variant
Dim lastRow As Long, lastCol As Long, i As Long, n As Long
Dim vals As Variant, keys As Object, k As Variant, rng As Range
Set src = ActiveSheet
hdr = InputBox("Enter the column header to split by (for example Region):")
If hdr = "" Then Exit Sub
col = Application.Match(hdr, src.Rows(1), 0)
If IsError(col) Then
MsgBox "Header '" & hdr & "' was not found in row 1."
Exit Sub
End If
lastRow = src.Cells(src.Rows.Count, col).End(xlUp).Row
lastCol = src.Cells(1, src.Columns.Count).End(xlToLeft).Column
If lastRow < 2 Then Exit Sub
On Error GoTo CleanUp
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
' Read the column once into memory and collect unique values
vals = src.Range(src.Cells(2, col), src.Cells(lastRow, col)).Value
Set keys = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(vals, 1)
If Not keys.Exists(CStr(vals(i, 1))) Then keys.Add CStr(vals(i, 1)), Empty
Next i
If src.AutoFilterMode Then src.AutoFilterMode = False
Set rng = src.Range(src.Cells(1, 1), src.Cells(lastRow, lastCol))
For Each k In keys.Keys
n = n + 1
Application.StatusBar = "Creating sheet " & n & " of " & keys.Count & ": " & k
If k = "" Then
rng.AutoFilter Field:=col, Criteria1:="="
Else
rng.AutoFilter Field:=col, Criteria1:=k
End If
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
ws.Name = SafeSheetName(CStr(k))
rng.SpecialCells(xlCellTypeVisible).Copy ws.Range("A1")
DoEvents
Next k
CleanUp:
If src.AutoFilterMode Then src.AutoFilterMode = False
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
If Err.Number <> 0 Then
MsgBox "Stopped at '" & k & "': " & Err.Description
Else
src.Activate
MsgBox keys.Count & " sheets created."
End If
End Sub
Function SafeSheetName(ByVal s As String) As String
Dim c As Variant
If s = "" Then s = "(blank)"
For Each c In Array("\", "/", "?", "*", "[", "]", ":")
s = Replace(s, c, "_")
Next c
SafeSheetName = Left(s, 31)
End Function
A few notes:
- Run it on a copy first, and delete any sheets from an earlier run. Excel won't allow two sheets with the same name, so the macro stops if a sheet for that value already exists.
- Text values work best as split keys (like Region). If the column holds dates or formatted numbers, AutoFilter matches the displayed text, so some values may not filter as expected.
- Sheet names are cleaned automatically: characters Excel doesn't allow are replaced and names are cut to 31 characters.
- The
DoEventsline keeps Excel responsive between sheets, so you shouldn't see "Not Responding" for long.
Roughly how many unique values does the column you split by have? If it's in the thousands, a different approach (writing arrays straight to each sheet) is faster.
If this runs smoothly on your large file, please consider accepting it as the answer. If it stops with an error, let me know the message and I'll be happy to keep helping.
Reference: