时间戳

首页 > 解决方案 > 如何根据空白/标准清除单元格

excel - 如何根据空白/标准清除单元格

问题描述

使用我当前的代码，当列（C：J）为空白时，我试图清除相应的单元格（列 K：N）。这是Rng的参考。我认为这是一个If Then声明，但不确定如何将其放入代码中......我知道这很长，但任何帮助都会很棒！

例如，如果 C30:J30 为空，则清除 K30:N30。它是一个If Then声明ClearContents吗？或者如果 C15:J15 为空白，则清除 K15:N15 等。

我正在寻找有关清除与“活动”标准位于同一行的单元格的帮助。但是，只有在它从“Future Project Hopper”复制到“CPD-Carryover,Complete&Active”之后。试图确保当我将 C 复制/清除到 J 并在 K 到 N 列中留下一些数据时不会造成混淆。我正在为其他人制作这个，以便轻松地将活动项目从一张纸移动到另一张纸。

Const cCrit As Variant = "D"      ' Criteria Column Letter/Number
Const cCols As String = "C:J"     ' Source/Target Data Columns
Const cFRsrc As Long = 15         ' Source First Row

Dim ws1 As Worksheet              ' Source Workbook
Dim ws2 As Worksheet              ' Target Workbook
Dim rng As Range                  ' Filter Range, Copy Range
Dim lRow As Long                  ' Last Row Number
Dim FRtgt As Long                 ' Target First Row
Dim Answer As VbMsgBoxResult      ' Message Box
Dim Error1 As VbMsgBoxResult      ' Message Box for Errors

' Create references to worksheets.
With ThisWorkbook
    Set ws1 = .Worksheets("Future Project Hopper")
    Set ws2 = .Worksheets("CPD-Carryover,Complete&Active")
End With

Answer = MsgBox("Do you want to run the Macro?", vbYesNo, "Run Macro")

If Answer <> vbYes Then Exit Sub

' In Source Worksheet
With ws1
    ' Clear any filters.
    .AutoFilterMode = False
    ' Calculate Last Row.
    lRow = .Cells(.Rows.Count, cCrit).End(xlUp).row
    ' Calculate Filter Column Range.
    Set rng = .Cells(cFRsrc, cCrit).Resize(lRow - cFRsrc + 1)
    ' Make an offset for the filter to start a row before (above) and
    ' end a row after (below).
    With rng.Offset(-1).Resize(lRow - cFRsrc + 3)
        ' Filter data in Criteria Column.
        .AutoFilter Field:=1, Criteria1:="Active"
    End With
    ' Create a reference to the Copy Range.
    Set rng = .Columns(cCols).Resize(rng.Rows.Count).Offset(cFRsrc - 1) _
            .SpecialCells(xlCellTypeVisible)

    ' Clear remaining filters.
    .AutoFilterMode = False

    End With

' Calculate Target First Row.
FRtgt = ws2.Cells(ws2.Rows.Count, cCrit).End(xlUp).row + 1
' Copy Range and paste to Target Worksheet and clear contents of future project hopper
rng.Copy
ws2.Columns(cCols).Resize(1).Offset(FRtgt - 1).PasteSpecial xlPasteValues
rng.Rows.ClearContents

标签： excelvba

解决方案

尝试：

Option Explicit

Sub test()

    Dim Counts As Long

    With ThisWorkbook.Worksheets("Sheet1")

        Counts = Application.WorksheetFunction.CountA(.Range("C30:J30"))

        If Counts = 0 Then
            .Range("K30:N30").ClearContents
        End If

    End With

End Sub

推荐阅读