在Excel中有效地复制可见/过滤的行 [英] Copying visible/filtered rows efficiently in excel

问题描述

我正在处理一些非常大的数据集(各具有65K +行和每列很多列的工作表).我正在尝试编写一些代码，以将过滤后的数据从一张纸尽可能快地复制到一个新的空纸上，但是到目前为止还没有取得太大的成功.

I am working with some very large datasets (various sheets with 65K+ rows and many columns each). I am trying to write some code to copy filtered data from one sheet to a new empty sheet as fast as possible, but have not had much success so far.

我可以根据请求包含其余代码，但是它所做的只是计算源范围和目标范围(srcRange和destRange).计算这些时间所花费的时间可以忽略不计.绝大多数时间都花在了这条线上(准确地说是4分50秒):

I can include the rest of the code by request, but all it does is calculates the source and destination ranges (srcRange and destRange). The time taken to calculate these is negligible. The vast majority of the time is being spent on this line (4 minutes 50 seconds to be precise):

srcRange.Rows.SpecialCells(xlCellTypeVisible).Copy Destination:=destRange

另外，我已经尝试过:

destRange.Value = srcRange.Rows.SpecialCells(xlCellTypeVisible).Value

但是当有过滤器时，它无法正常工作.

But it doesn't work properly when there's a filter.

Function FastCopy(srcSheet As String, srcCol As String, destSheet As String, destCol As String)

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim srcRange As Range
    Dim destRange As Range

    Set srcRange = GetColumnRangeByHeaderName(srcSheet, srcCol, -1)
    Set destRange = GetColumnRangeByHeaderName(destSheet, destCol, srcRange.Rows.Count)


    'destRange.Value = srcRange.Rows.SpecialCells(xlCellTypeVisible).Value

    srcRange.Rows.SpecialCells(xlCellTypeVisible).Copy Destination:=destRange

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationManual
End Function

这是一台速度慢的双核计算机，具有2GB RAM，在excel 2010中运行.在速度更快的计算机上，结果显然会有所不同.

This is a slow, dual core machine with 2GB of RAM running excel 2010. Results will obviously vary on a faster machine.

推荐答案

尝试使用类似方法来处理过滤范围.您在正确的轨道上，.Copy方法很昂贵，并且简单地从范围到范围写入值应该快得多，但是，正如您观察到的那样，当范围被过滤时，这是行不通的.过滤范围后，您需要迭代范围.SpecialCells中的.Areas:

Try something like this to work with filtered ranges. You're on the right track, the .Copy method is expensive and simply writing values from range to range should be much faster, however as you observe, this doesn't work when a range is filtered. When the range is filtered, you need to iterate the .Areas in the range's .SpecialCells:

Sub Test()
Dim rng As Range
Dim subRng As Range
Dim destRng As Range


Set destRng = Range("A10")

Set rng = Range("A1:B8").SpecialCells(xlCellTypeVisible)

For Each subRng In rng.Areas
    Set destRng = destRng.Resize(subRng.Rows.Count, subRng.Columns.Count)
    destRng.Value = subRng.Value
    Set destRng = destRng.Cells(destRng.Rows.Count, 1).Resize(1, 1).Offset(1, 0)
Next

End Sub

已针对您的目的进行了修改，但未经测试:

Modified for your purposes, but untested:

Function FastCopy(srcSheet As String, srcCol As String, destSheet As String, destCol As String)

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim srcRange As Range
    Dim destRange As Range
    Dim subRng As Range

    Set srcRange = GetColumnRangeByHeaderName(srcSheet, srcCol, -1)
    Set destRange = GetColumnRangeByHeaderName(destSheet, destCol, srcRange.Rows.Count)

    For Each subRng In srcRange.Areas
        Set destRng = destRng.Resize(subRng.Rows.Count, subRng.Columns.Count)
        destRng.Value = subRng.Value
        Set destRng = destRng.Cells(destRng.Rows.Count, 1).Resize(1, 1).Offset(1, 0)
    Next

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationManual
End Function

这篇关于在Excel中有效地复制可见/过滤的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！