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

问题描述

以下代码允许我删除不符合我的条件的行，现在问题是需要大约52分钟，因为我的行超过了1,68,000，这将持续增加每周。
现在我正在使用过滤器来减少基于我的cnf和条件的数据，因为日期范围不能被过滤掉。但这不是一个选择。我必须根据我的日期比较范围删除行。似乎数组是我最后的手段，但我不知道如何将我的工作表存储在一个数组中并且在它们上工作。有人可以指导我吗？

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 ?

[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

推荐答案

进一步了解我们的聊天讨论，这里是我的自动过滤器方法。

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

让我们说你的数据看起来像这样

Let's say your data looks like this

现在我们来说说 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屋！