将工作表存储在数组中并对其进行处理 [英] Storing a worksheet in an array and working on it

问题描述

下面的代码删除不符合我的条件的行.当我的行数超过1,68,000时，大约需要52分钟，而且这个数字还将继续增加.

The below code deletes rows which do not match my criteria. It takes around 52 minutes as my rows exceed 1,68,000 and this will keep increasing.

我正在使用过滤器来减少数据.这不再是一种选择.我必须根据日期比较范围删除行.看来数组是我的最后选择，但我不知道如何将工作表存储在数组中并对其进行处理.

I was using filters to reduce the data. That isn't an option any more. I have to delete rows based on my date compare range. It seems an array is my last resort, but I do not know how to store my worksheets in an array and work on them.

如何将工作表存储在数组中并对其进行处理?

How do I store my worksheet in an array and work on it?

' to delete data not meeting criteria
Worksheets("Dashboard").Activate
n1 = Range("n1")
n2 = Range("n2")
Worksheets("Temp Calc").Activate
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For z = lastrow To 2 Step -1
     If Cells(z, 6).Value = "CNF" Or Cells(z, 4).Value <= n1 Or Cells(z,3).Value >= n2 Then
          Rows(z).Delete
     End If
Next z

这是基本的东西.

Dim arr1(),  dim arr2() as variant
lastrow  = cells(Rows.count,1).End(XlUp).Row
lastcol = cells(1,column.count).End(xlRight).Column
arr1(lastrow,lastcol) <- I dont know if this is correct. 
<------How do I copy/paste my data into the array? ----->

<This is what I came up with for deleting what I dont need.>
For x=lastrow to 2 Step -1
If arr1(x,6)<>"" or arr1(x,6)<>"CNF" And arr(x,4)>=n1 And arr(x,3)<=n2 then
For k = lastrow to 2 
<I dont know how to delete rows in an array.
rows(x).delete ?

推荐答案

进一步讨论我们的聊天讨论，这是我的自动筛选方法.

Ok further to our chat discussions, here is my autofilter approach.

您的要求

首先，我想删除cole 6中具有"的行 我也将两个日期存储在变量n1和n2中 现在如果col 4> n1并且col 3 <; n2然后删除 请忽略CNF条件，因为我的一些数据对此有一些例外，我想保留

First i want to delete rows in cole 6 that have "" also i am storing two dates in variables n1 and n2 now if col 4 > n1 And col 3 < n2 Then delete please ignore CNF condition as some of my data has some exceptions to this which i want to keep

假设您的数据如下所示

现在让我们说N1 = 5/1/2012和N2 = 7/1/2012

如果您直观地看到屏幕截图，那么您会注意到只有一个符合条件的行，即第9行(员工623 ***).

If you visually see the screenshot then you will notice that there is only one row which meets the condition and that is Row 9 (Employee 623***).

代码

我已经注释了代码，以便您在理解它时不会遇到问题.

I have commented the code so that you will not have a problem understanding it.

Sub Sample()
    Dim ws As Worksheet
    Dim FltrRng As Range
    Dim lRow As Long
    Dim N1 As Date, N2 As Date

    Set ws = ThisWorkbook.Worksheets("Temp Calc")

    '~~> Start Date and End Date
    N1 = #5/1/2012#: N2 = #7/1/2012#

    With ws

        '~~> Remove any filters
        .AutoFilterMode = False

        '~~> Get the last row
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Identify your data range
        Set FltrRng = .Range("A1:F" & lRow)

        '~~> Filter the data as per your criteria
        With FltrRng
            '~~> First filter on blanks
            .AutoFilter Field:=6, Criteria1:="="
            '~~> Next filter on Start Date
            .AutoFilter Field:=3, Criteria1:=">" & N1, Operator:=xlAnd
            '~~> Finally filter on End Date
            .AutoFilter Field:=4, Criteria1:="<" & N2, Operator:=xlAnd
            '
            '~~> And so on if required
            '

            '~~> Delete the filtered rows
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

截图后

如果您发现所需的记录已被删除.

If you notice that the required record has been deleted.

这篇关于将工作表存储在数组中并对其进行处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！