Excel 表达式复制行但删除空白行

Question

我需要将数据从一个工作表复制到另一个工作表。但是，我需要一个条件复制操作，它将根据条件跳过行。

例如，如果我开始...

Active  Value
yes     1
no      2
no      3
yes     4
no      5
no      6

我只想复制 Active=yes 的行，所以我最终会...

Value
1
4

有人可以告诉我这是如何使用 1)宏和 2)公式完成的吗？

Answer 1

公式方法:

假设您的数据在 sheet1 中，范围为 A2:B7 .

然后在 sheet2 单元格 A2 中使用此公式:

=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$2:$A$7="yes",ROW(Sheet1!$A$2:$A$7)),ROW()-ROW($A$2)+1)),"")

使用数组条目 (CTRL+SHIFT+ENTER)，然后将其向下拖动。



VBA方法:

您可以使用 AutoFilter :

Sub test()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rng As Range, rngToCopy As Range
    Dim lastrow As Long
    'change Sheet1 and Sheet2 to suit
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")

    With ws1
        'assumung that your data stored in column A:B, Sheet1
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng = .Range("A1:B" & lastrow)
        'clear all filters
        .AutoFilterMode = False
        With rng
            'apply filter
            .AutoFilter Field:=1, Criteria1:="yes"
            On Error Resume Next
            'get only visible rows
            Set rngToCopy = .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
        End With
        'copy range
        If Not rngToCopy Is Nothing Then rngToCopy.Copy Destination:=ws2.Range("A1")
        'clear all filters
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
End Sub

注意，如果您只想复制 Value列，改变

Set rngToCopy = .SpecialCells(xlCellTypeVisible)

至

Set rngToCopy = .Offset(, 1).Resize(, .Columns.Count - 1).SpecialCells(xlCellTypeVisible)