Hi All!
I have the table below. Usually, this table contains a few hundred thousand rows (packaging recordings for statistics). I want to split the sheet based on Column B (VarName) in that way when I have a B cell containing the DatiStatistica_targetWeight value and then copy the rows after it to a new sheet until a new DatiStatistica_targetWeight value appears. The code works well when I have small data numbers, but with the huge amount of data, the PC freeze. The PC is a Dell Ryzen 5 chip and 16GB memory. And everything is running on max 51%. Is there possible to reduce the computation need for the code? Or any advice for optimizing this?
Thanks!
The code is:
I have the table below. Usually, this table contains a few hundred thousand rows (packaging recordings for statistics). I want to split the sheet based on Column B (VarName) in that way when I have a B cell containing the DatiStatistica_targetWeight value and then copy the rows after it to a new sheet until a new DatiStatistica_targetWeight value appears. The code works well when I have small data numbers, but with the huge amount of data, the PC freeze. The PC is a Dell Ryzen 5 chip and 16GB memory. And everything is running on max 51%. Is there possible to reduce the computation need for the code? Or any advice for optimizing this?
Thanks!
The code is:
VBA Code:
Sub SeparateTableByColumnB()
Application.ScreenUpdating = False
Dim srcTable As ListObject
Dim srcRow As Range
Dim dstWorkbook As Workbook
Dim dstWorksheet As Worksheet
Dim dstRow As Long
Dim dstRowStart As Long
' Set the source table
Set srcTable = ActiveSheet.ListObjects("Table1")
' Loop through each row in the table
For Each srcRow In srcTable.DataBodyRange.Rows
' Check if column B contains "DatiStatistica_TargetWeight"
If srcRow.Cells(2).Value = "DatiStatistica_TargetWeight" Then
' Create a new workbook if one does not already exist
If dstWorkbook Is Nothing Then
Set dstWorkbook = Workbooks.Add
End If
' Add a new worksheet to the destination workbook
Set dstWorksheet = dstWorkbook.Worksheets.Add
' Copy the row to the destination worksheet
dstRowStart = dstWorksheet.Cells(dstWorksheet.Rows.Count, 1).End(xlUp).Row + 1
srcRow.Resize(srcRow.Rows.Count, srcRow.Columns.Count).Copy dstWorksheet.Cells(dstRowStart, 1)
dstRow = dstRowStart + srcRow.Rows.Count - 1
Else
' Copy the row to the current destination worksheet
If Not dstWorksheet Is Nothing Then
srcRow.Resize(srcRow.Rows.Count, srcRow.Columns.Count).Copy dstWorksheet.Cells(dstRow + 1, 1)
dstRow = dstRow + srcRow.Rows.Count
End If
End If
Next srcRow
' Autofit all columns in the destination worksheet
dstWorksheet.Columns.AutoFit
Application.ScreenUpdating = True
End Sub
Csomi1.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Forrás.Név | VarName | TimeString | VarValue | Validity | Time_ms | ||
2 | DatiProduzione0.csv | DatiStatistica_TargetWeight | 20/02/2023 08:10 | 700 | 1 | 4,498E+14 | ||
3 | DatiProduzione0.csv | Recipes_Recipe{0}_TollMinus | 20/02/2023 08:10 | 30 | 1 | 4,498E+14 | ||
4 | DatiProduzione0.csv | Recipes_Recipe{0}_TollPlus | 20/02/2023 08:10 | 100 | 1 | 4,498E+14 | ||
5 | DatiProduzione0.csv | DatiStatistica_TargetWeight | 20/02/2023 08:11 | 700 | 1 | 4,498E+14 | ||
6 | DatiProduzione0.csv | Recipes_Recipe{0}_TollMinus | 20/02/2023 08:11 | 30 | 1 | 4,498E+14 | ||
7 | DatiProduzione0.csv | Recipes_Recipe{0}_TollPlus | 20/02/2023 08:11 | 100 | 1 | 4,498E+14 | ||
8 | DatiProduzione0.csv | DatiStatistica_TargetWeight | 20/02/2023 08:57 | 700 | 1 | 4,498E+14 | ||
9 | DatiProduzione0.csv | Recipes_Recipe{0}_TollMinus | 20/02/2023 08:57 | 30 | 1 | 4,498E+14 | ||
10 | DatiProduzione0.csv | Recipes_Recipe{0}_TollPlus | 20/02/2023 08:57 | 100 | 1 | 4,498E+14 | ||
11 | DatiProduzione0.csv | DatiStatistica_TargetWeight | 20/02/2023 09:34 | 700 | 1 | 4,498E+14 | ||
12 | DatiProduzione0.csv | Recipes_Recipe{0}_TollMinus | 20/02/2023 09:34 | 30 | 1 | 4,498E+14 | ||
13 | DatiProduzione0.csv | Recipes_Recipe{0}_TollPlus | 20/02/2023 09:34 | 100 | 1 | 4,498E+14 | ||
14 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 09:35 | 700 | 1 | 4,498E+14 | ||
15 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 09:35 | 700 | 1 | 4,498E+14 | ||
16 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 09:35 | 700 | 1 | 4,498E+14 | ||
17 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 09:35 | 699 | 1 | 4,498E+14 | ||
18 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 09:35 | 683 | 1 | 4,498E+14 | ||
19 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 09:35 | 700 | 1 | 4,498E+13 | ||
20 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 09:35 | 700 | 1 | 4,498E+14 | ||
21 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 09:35 | 700 | 1 | 4,498E+14 | ||
22 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 09:35 | 700 | 1 | 4,498E+14 | ||
23 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 09:35 | 700 | 1 | 4,498E+14 | ||
24 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 09:35 | 699 | 1 | 4,498E+14 | ||
25 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 09:35 | 699 | 1 | 4,498E+14 | ||
26 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 09:35 | 700 | 1 | 4,498E+14 | ||
27 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 09:35 | 700 | 1 | 4,498E+14 | ||
28 | DatiProduzione0.csv | DatiStatistica_TargetWeight | 20/02/2023 09:34 | 700 | 1 | 4,498E+14 | ||
29 | DatiProduzione0.csv | DatiStatistica_TargetWeight | 20/02/2023 15:27 | 1400 | 1 | 4,498E+14 | ||
30 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 15:27 | 1400 | 1 | 4,498E+14 | ||
31 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 15:27 | 1400 | 1 | 4,498E+14 | ||
32 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 15:27 | 1400 | 1 | 4,498E+14 | ||
33 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 15:27 | 1400 | 1 | 4,498E+14 | ||
34 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 15:27 | 1400 | 1 | 4,498E+14 | ||
35 | DatiProduzione0.csv | DatiStatistica_CombiWeight | 20/02/2023 15:27 | 1400 | 1 | 4,498E+14 | ||
Sheet1 |