Understanding VBA (Excel) Processing Speed

If you read the log file (about 70,000 to 80,000 lines) and there is a blank space in column A,
This is the process of deleting the column, but this process alone is
It will take about 3-5 minutes.
Excel takes this long to process 70,000 to 80,000 lines.
80,000 lines. Is it something to put away?

The PC has 16GB of Win10procore i7 memory.

Subtest()
    DimtxtPath As String
    Dim TmpMaxRow As Long
    
    txtPath="C:\sample.log"
    
    Workbooks.OpenText Filename: =txtPath,_
        DataType: =xlDelimited,_
        Tab: = True, _
        Space: = True
    
US>'Delete column if column A has blank lines
    TmpMaxRow=Cells(Rows.Count, "A").End(xlUp).Row
    Range("A1:A" & TmpMaxRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
end sub

vba excel

2022-09-30 15:57

1 Answers

Excel cell operations are very CPU- and memory-intensive, so they tend to get heavy.Some of the trick techniques include "manual recalculation" and "turn off screen redrawing," but the effect is limited.The essential solution is to do everything you can, such as deleting unnecessary lines, before writing data to Excel cells.

For example, ADO treats text files as SQL tables and queries them.It takes 1-2 seconds to load 100,000 lines in my environment (Core i5/16GB).

Dim Conn As Object
Dim Command As Object
Dim Recordset As Object
    
Set Conn=CreateObject("ADODB.Connection")
Conn.Provider= "Microsoft.Ace.OLEDB.12.0"
Conn. Properties ("Data Source") = "C:"
Conn.Properties("Extended Properties") = "Text;"
Conn.Open
    
Set Command=CreateObject("ADODB.Command")
Set Command.ActiveConnection=Conn
Command.CommandText="SELECT* FROM [sample.log] WHERE NOT IsNull (F1)"
    
Set Recordset=Command.Execute()
    
Range("A1").CopyFromRecordsetRecordsetRecordset

This is a detailed supplement, but if you want to handle tab-separated files in this way, you must create a file called schema.ini in the same folder as the original data.The following is an example of the content, but please refer to the Microsoft documentation for more information.
https://docs.microsoft.com/ja-jp/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-ver16

 [sample.log]
ColNameHeader=False
Format = TabDelimited
Col1 = F1 Text
Col2=F2Text

2022-09-30 15:57

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656