时间戳

首页 > 解决方案 > 选择范围取决于两个关键词

excel - 选择范围取决于两个关键词

问题描述

我目前正在做一个项目来创建一个 VBA 来自动删除空行。我在一些帮助下创建了以下内容：

    Dim rngFirst As Range
    Dim rngLast As Range

    Application.ScreenUpdating = False

    With Sheets("Input")
        'Find the start and stop
        Set rngFirst = .Cells.Find(what:="[Performance Income]", lookat:=xlWhole, _
            LookIn:=xlValues, MatchCase:=False)
        Set rngLast = .Cells.Find(what:="[Miscellaneous Income]", _
            lookat:=xlWhole, LookIn:=xlValues, MatchCase:=False)

        .Range(rngFirst, rngLast).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With

    Application.ScreenUpdating = True

我遇到 1004 运行时错误。我已经搜索了我认为这与我的范围选择有关的原因。不过具体不知道，希望能得到帮助。先感谢您。

标签： excelvba

解决方案

这对我有用。

您必须确保第一个范围是最后一个之前的单元格，并且它们位于同一列中。否则会引发错误。

要定义新范围，请使用开始范围和结束范围的地址属性。

Sub DeleteBlankCells()

    Dim rngFirst As Range
    Dim rngLast As Range

    Dim rngUnion As Range
    Application.ScreenUpdating = False

    With Sheets("Input")
        'Find the start and stop
        Set rngFirst = .Cells.Find(what:="Performance Income", lookat:=xlWhole, _
            LookIn:=xlValues, MatchCase:=False)
        Set rngLast = .Cells.Find(what:="Miscellaneous Income", _
            lookat:=xlWhole, LookIn:=xlValues, MatchCase:=False)

        Set rngUnion = Range(rngFirst.Address, rngLast.Address)

        rngUnion.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    End With

    Application.ScreenUpdating = True

End Sub

推荐阅读