如何将行数据计算为数组？

Question

有人能解释我如何将我的行e计算为数组而不是在单个(行)级别上计算吗？这是提高性能的最好方法吗？

Dim x As Integer, i As Integer, y As Long

Set wsCountry = Sheets("Country Data")
Set wsInstructions = Sheets("Instructions")

LastRow = wsCountry.Range("E" & Rows.Count).End(xlUp).Row

For x = 9 To 9
    For i = 9 To LastRow

If wsCountry.Range("E" & i) <> wsInstructions.Range("C" & x) Then
   wsCountry.Range("A" & i & ":P" & i).ClearContents
End If

    Next i

'Delete Blanks
For y = Cells(Rows.Count, 3).End(xlUp).Row To 1 Step -1
    If Cells(y, 3) = "" Then
        Rows(y).Delete
    End If
Next y


'Save workbook AS to FILE

Next x

Answer 1

使用2 AutoFilters：

1. 删除列C包含空值的行
2. 删除E列不包含指令表单元格C9中的值的行

。

Option Explicit

Sub parseRows()
    Dim wsCountry As Worksheet, wsInstructions As Worksheet
    Dim lastRow As Long, instructionsVal As String, rowRng As Range

    Set wsCountry = Worksheets("Country Data")
    Set wsInstructions = Worksheets("Instructions")
    instructionsVal = wsInstructions.Range("C9")

    Application.ScreenUpdating = False

    lastRow = wsCountry.Cells(wsCountry.Rows.Count, 5).End(xlUp).Row
    Set rowRng = wsCountry.Range("C8:C" & lastRow)

    With wsCountry.UsedRange    'Filter col 3 (C) - make blanks visible
        .AutoFilter Field:=3, Criteria1:="="
        rowRng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With

    lastRow = wsCountry.Cells(wsCountry.Rows.Count, 5).End(xlUp).Row
    Set rowRng = wsCountry.Range("E8:E" & lastRow)

    With wsCountry.UsedRange    'Filter col 5 (E) - values <> instructionsVal
        .AutoFilter Field:=5, Criteria1:="<>" & instructionsVal
        rowRng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With

    Application.ScreenUpdating = True
End Sub

Answer 2

你的代码看上去很好。但是，您要求的是最佳性能。这将通过循环数组来完成，而不是范围，当范围很大时，范围会变慢。最好将范围转储到数组中，如下所示：

Dim Arr() As Variant
Arr = Sheet1.Range("A1:B10")

然后在数组上循环。因为这会创建一个2d数组，所以您可以选择只在一个维度(行)上循环。我希望我正确回答了你的问题。