Excel crashing while running VBA on large data set

Payal Gupta 0 Reputation points
2026-11-19T09:17:57.15+00:00

Title: Excel VBA Macro Not Responding When Splitting Large Datasets into Multiple Worksheets

Hello Microsoft Community,

I am using the following VBA macro in Microsoft Excel to split a large dataset into individual worksheets based on a selected column (for example, Region).

The macro works perfectly with small datasets. However, when I run it on a large dataset, Excel becomes extremely slow and eventually displays "Not Responding."

My System Configuration:

  • Processor: Intel Core i7 13th Generation
  • RAM: 16 GB
  • Storage: 512 GB SSD
  • Application: Microsoft Excel

What the VBA macro does:

  1. Takes the active worksheet as the source.
  2. Asks the user to enter a column header (such as Region).
  3. Identifies all unique values in the selected column.
  4. Creates a separate worksheet for each unique value.
  5. Copies the header row and all matching records into the respective worksheets.

My issue:

The macro uses multiple loops to check every row for each unique value and copies rows individually. I suspect this is causing performance issues when processing large datasets.

For example, if my dataset contains 100,000–500,000 rows and multiple regions, Excel becomes unresponsive while the macro is running.

What I need help with:

  1. How can I optimize this VBA code to handle large datasets efficiently?
  2. Is it possible to use arrays, AutoFilter, dictionaries, or another faster approach instead of copying rows individually?
  3. Can the macro process hundreds of thousands of rows without making Excel freeze or display "Not Responding"?
  4. Can we improve memory management and reduce unnecessary worksheet operations?
  5. Is there a way to display progress while the macro is running?

Important: I want to retain the existing functionality. The macro should still ask the user to select a column header and automatically create individual worksheets for each unique value in that column.

I would appreciate an optimized VBA solution and suggestions for handling large datasets efficiently.

Thank you!Title: Excel VBA Macro Not Responding When Splitting Large Datasets into Multiple Worksheets

Hello Microsoft Community,

I am using the following VBA macro in Microsoft Excel to split a large dataset into individual worksheets based on a selected column (for example, Region).

The macro works perfectly with small datasets. However, when I run it on a large dataset, Excel becomes extremely slow and eventually displays "Not Responding."

My System Configuration:

  • Processor: Intel Core i7 13th Generation
  • RAM: 16 GB
  • Storage: 512 GB SSD
  • Application: Microsoft Excel

What the VBA macro does:

  1. Takes the active worksheet as the source.
  2. Asks the user to enter a column header (such as Region).
  3. Identifies all unique values in the selected column.
  4. Creates a separate worksheet for each unique value.
  5. Copies the header row and all matching records into the respective worksheets.

My issue:

The macro uses multiple loops to check every row for each unique value and copies rows individually. I suspect this is causing performance issues when processing large datasets.

For example, if my dataset contains 100,000–500,000 rows and multiple regions, Excel becomes unresponsive while the macro is running.

What I need help with:

  1. How can I optimize this VBA code to handle large datasets efficiently?
  2. Is it possible to use arrays, AutoFilter, dictionaries, or another faster approach instead of copying rows individually?
  3. Can the macro process hundreds of thousands of rows without making Excel freeze or display "Not Responding"?
  4. Can we improve memory management and reduce unnecessary worksheet operations?
  5. Is there a way to display progress while the macro is running?

Important: I want to retain the existing functionality. The macro should still ask the user to select a column header and automatically create individual worksheets for each unique value in that column.

I would appreciate an optimized VBA solution and suggestions for handling large datasets efficiently.

Thank you!

Microsoft 365 and Office | Excel | For home | Windows
0 comments No comments

1 answer

Sort by: Most helpful
  1. Rukshan edirisinghe 2,930 Reputation points
    2026-11-19T16:33:32.52+00:00

    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 DoEvents line 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:

    Was this answer helpful?

    0 comments No comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.