excel - 如何将行数据评估为数组？

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 个自动过滤器:

删除 C 列包含空白值的行

删除 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