优化行删除 [英] Optimization on row deletion

问题描述

我有一个很大的CSV文件(> 10000行)，数据如下:

I have a big CSV file (>10000 lines) with data like:

126142448.465    320351690.1984   606472348.901 6  130544738.78842  320252387.00343
-3021.759       -2354.621  
743.981    nan        979.988 5  nan        nan       
4131.358         nan  

为了减少处理的信息量，我将信息放在单行中，因此之后，我需要删除很多行. 删除它们需要太多时间.我该如何减少呢?

In order to reduce the volume of information to work with, I am placing the information in single rows so, after that, I need to delete a lot of rows. It is taking too much time to delete them. How can I reduce it?

导入的数据文件: 示例1

精简数据文件: 示例2

在图像上可以清楚地看到生成的空行的数量.我不能认为这是一对，因为某些原始行是单行的.

On the image it is clear the amounts of empty rows generated. I can not consider that are pair ones as some of the original lines are single.

我尝试删除没有屏幕活动的空行.但是删除3000行大约需要50秒.

I have tried to delete the empty rows with no screen activity. But it is taking about 50 second to delete 3000 rows.

经过多次试验，尝试优化代码:

After many trials, trying to optimize the code:

Dim LastEl As Long
With ThisWorkbook
    Set wsRaw = .Worksheets("RAW")
End With

...

wsRaw.Activate 
LastEl = wsRaw.Cells.SpecialCells(xlLastCell).Row
Do Until LastEl = 0
    If WorksheetFunction.CountA(Rows(LastEl)) = 0 Then
    Rows(LastEl).Delete
    End If
    LastEl = LastEl - 1
Loop

结果还可以，但是时间不对. 有帮助吗?

The results are OK but the time is not. Any help?

推荐答案

方法1: 代替循环，您可以使用以下行:

wsRaw.Range("A1:A" & LastEl).SpecialCells(xlCellTypeBlanks).EntireRow.Delete xlUp

代码:

Dim LastEl As Long
With ThisWorkbook
    Set wsRaw = .Worksheets("RAW")
End With

wsRaw.Activate
LastEl = wsRaw.Cells.SpecialCells(xlLastCell).row

wsRaw.Range("A1:A" & LastEl).SpecialCells(xlCellTypeBlanks).EntireRow.Delete xlUp

鉴于空白列"表示该行为空白.

Given that Column Blank means the row is blank.

方法2:将所有空白行添加到一个范围并立即删除.

Dim mysel As Range

Dim LastEl As Long
With ThisWorkbook
    Set wsRaw = .Worksheets("RAW")
End With

wsRaw.Activate
LastEl = wsRaw.Cells.SpecialCells(xlLastCell).row
Do Until LastEl = 0
    If WorksheetFunction.CountA(Rows(LastEl)) = 0 Then

        If mysel Is Nothing Then
            Set mysel = Rows(LastEl)
        Else
            Set mysel = Union(mysel, Rows(LastEl))
        End If

    End If
    LastEl = LastEl - 1
Loop


mysel.Delete xlUp

这篇关于优化行删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！