将工作表存储在数组中并处理它们

the below code allows me to delete rows based which do not match my criteria, now the problem is it takes around 52 minutes as my rows exceed 1,68,000 and this will keep increasing on a weekly basis. now i was using filters to reduce the data based on my cnf and condition since the date range cannot be filtered out. But that isn't an option any more. i have to delete rows based on my date compare range. it seems array is my last resort but i do not know how to store my worksheet in an array and work on them. Could somebody please guide me ?

下面的代码允许我删除与我的标准不符的行,现在问题是大约需要52分钟,因为我的行超过1,68,000,这将每周增加。现在我使用过滤器来减少基于我的cnf和条件的数据,因为日期范围无法过滤掉。但这不再是一种选择。我必须根据我的日期比较范围删除行。似乎数组是我的最后手段,但我不知道如何将我的工作表存储在数组中并对它们进行处理。有人可以指导我吗?

[CODE]
' 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
[/CODE]

this is something basic is was trying, keep i mind i know very little about array so the following may be wrong and incomplete

这是基本的尝试,请记住我对阵列知之甚少,所以以下可能是错误的和不完整的

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 ?

i suck at arrays. Any help is appreciated

我吮吸数组。任何帮助表示赞赏

2 个解决方案

#1

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

我们的聊天讨论更进一步,这是我的自动过滤方法。

Your Requirement

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

首先,我想删除cole 6中有“”的行,“我也在变量n1和n2中存储两个日期,如果col 4> n1和col 3 那么删除请忽略cnf条件,因为我的一些数据有一些例外这是我想保留的

Let's say your data looks like this

假设您的数据看起来像这样

Now let's say N1 = 5/1/2012 and N2 = 7/1/2012

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

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***).

如果您在视觉上看到屏幕截图,那么您会注意到只有一行符合条件,即第9行(员工623 ***)。

Code

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

After Screenshot

If you notice that the required record has been deleted.

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

#2

Try This :

试试这个 :

         Dim varArrdata   as variant
         Dim lngloop      as long
         Dim strRows      as string
         vararrdata = Range(Cells(1, 1), Cells(Rows.Count, 6).End(xlUp)) ' OR use Range("A1").CurrentRegion
         For lngLoop = LBound(vararrdata) To UBound(vararrdata)
            If vararrdata(lngLoop, 6) = "CNF" Or vararrdata(lngLoop, 4) <= [n1] Or vararrdata(lngLoop, 3) >= [n2] Then
                strRows = strRows & "|" & lngLoop
            End If
         Next
         vararrdata = Split(Mid(strRows, 2), "|")
         Range("A" & Join(vararrdata, ",A")).EntireRow.Delete