Hi there.
I've got a VBA macro that I adapted from @Fluff here (thank you!).
Here's my edited code:
Given the volume of work I need to do, I need something that runs more quickly.
In a nutshell, I've got a series of CSV exports that I'm trying to break up and save as text files. Column A contains a ThreadID value that repeats. Rows are clustered by ThreadID. I'm trying to select all contiguous rows bearing the same ThreadID and save those as a single Text file named by that ThreadID. Columns A:I are populated with data and that's what I'm looking to save in those text files.
@Fluff 's code works great. But I'll need to process 200-250 CSV files, some of which have thousands of ThreadID clusters. Based upon my tests of the existing code, it will take over a week to execute across dozens of source CSV files.
Any suggestions on how to speed things up? Open to alternate code.
Thanks!
I've got a VBA macro that I adapted from @Fluff here (thank you!).
Here's my edited code:
VBA Code:
Sub SaveAsTextFiles1()
'https://www.mrexcel.com/board/threads/select-contiguous-values-in-col-a.1089184/
Dim Cl As Range
Dim ws As Worksheet
Dim Ky As Variant
Application.DisplayAlerts = False 'Suppresses clipboard warning message
Application.ScreenUpdating = False 'Prevents flickering of screen / change of focus
Application.Calculation = xlCalculationManual 'Turns off calculations while code runs
Set ws = Sheets("sheet1")
With CreateObject("scripting.dictionary")
For Each Cl In ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp))
If Not .Exists(Cl.Value) Then .Add Cl.Value, Nothing
Next Cl
For Each Ky In .Keys
ws.Copy
Range("A1:I1").AutoFilter 1, "<>" & Ky
ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
Range("A1:I1").AutoFilter
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Ky, 20
ActiveWorkbook.Close False
Next Ky
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Beep
MsgBox "Congratulations!" & vbNewLine & vbNewLine & "Your macro has completed."
End Sub
Given the volume of work I need to do, I need something that runs more quickly.
In a nutshell, I've got a series of CSV exports that I'm trying to break up and save as text files. Column A contains a ThreadID value that repeats. Rows are clustered by ThreadID. I'm trying to select all contiguous rows bearing the same ThreadID and save those as a single Text file named by that ThreadID. Columns A:I are populated with data and that's what I'm looking to save in those text files.
@Fluff 's code works great. But I'll need to process 200-250 CSV files, some of which have thousands of ThreadID clusters. Based upon my tests of the existing code, it will take over a week to execute across dozens of source CSV files.
Any suggestions on how to speed things up? Open to alternate code.
Thanks!