如果更改列中的单元格为空，则删除行 [英] Delete row if cell in changing column is blank

问题描述

这是Windows 7上的Excel 2010.

This is is Excel 2010 on Windows 7.

我收到电子表格，其中其中一列称为已批准".此列填充有x和空格.我要删除该列中所有具有空白的行.这是一个简单的问题，但有两个令人困惑的问题:

I receive spreadsheets where one of the columns is called "Approved." This column is filled with x's and blanks. I want to delete all rows that have blanks in that column. This is a simple problem but has two confounding issues:

1. 批准"列的位置发生了变化，因此我不能只做Columns("R").SpecialCells(xlBlanks).EntireRow.Delete.我尝试通过在A1:Z5中搜索批准"来解决该问题(因为总是少于26行)，然后选择在其中找到列.
2. 许多数据是从上个月的文档中提取的，因此某些空白"单元格中填充了一个vlookup.我尝试通过首先选择所有数据并将其粘贴为值来解决此问题.

这是当前代码:

Sub DeleteCol()
Range("A1").Select
Range(Selection, Selection.SpecialCells(xlLastCell)).Select
Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

  Dim rngApprove As Range
  Set rngApprove = Range("A1:Z5").Find("Approve")
  If rngApprove Is Nothing Then
    MsgBox "Approved column was not found."
    Exit Sub
  End If
  Dim approved_column As Range
  Set approved_column = rngApprove.EntireColumn
  approved_column.SpecialCells(xlBlanks).EntireRow.Delete
End Sub

复制+粘贴为值可以正常工作.但是，即使其中某些单元格为空，删除行也只会删除第1-4行，并仅保留第5行以下的所有内容.如果我用

The copy + paste as value works as intended. However, the row deletion only deletes rows 1-4 and leaves everything below row 5 alone, even though some of those cells are blank. If I replace the last line with

approved_column.select

它会选择整个列.这使我相信问题出在我的删除方法上.

it selects the whole column, as it should. This leads me to believe that the issue is with my deletion method.

推荐答案

如果公式返回的零长度字符串不足以将公式结果还原为它们的值.您需要使用 Range.TextToColumns方法快速清除该列，使用固定宽度"并将列的值返回到其原始单元格，以使单元格真正为空白.

If you have zero-length strings returned by formulas, it is not sufficient to revert the formula results to their values. You need to quickly sweep the column with a Range.TextToColumns method, using Fixed Width and returning the column's values back to their original cells to make the cells truly blank.

Sub DeleteCol()
    Dim iCOL As Long, sFND As String

    With ActiveSheet
        With .Range(.Cells(1, 1), .Cells(1, 1).SpecialCells(xlLastCell))
            .Value = .Value
        End With
        sFND = "Approve"

        If CBool(Application.CountIf(.Rows(1), sFND)) Then
            iCOL = Application.Match(sFND, .Rows(1), 0)
            If CBool(Application.CountBlank(.Columns(iCOL))) Then
                With .Columns(iCOL)
                    .TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, _
                                   FieldInfo:=Array(0, 1)
                    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
                End With
            End If
        End If
    End With

End Sub

工作表的 COUNTBLANK函数会将零长度字符串计入其空白计数，以便我们在继续操作之前确定是否存在空白单元格.使用 COUNTIF函数，以确保第一行中的标题为"Approve".

The worksheet's COUNTBLANK function will count zero-length strings in its blank count so we can determine whether there are blank cells before proceeding. The same goes for using the COUNTIF function to make sure that there is a column header with 'Approve' in the first row.

这篇关于如果更改列中的单元格为空，则删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！