如何更新代码以删除空格，同时删除特定的“字符串"? [英] How can I update code to delete blanks to also delete a specific "string"?

问题描述

如果A列中的单元格为空白，如何更新代码以删除该行，如果A列中的单元格包含字符串"Gender"，如何删除该行?

How can I update code to delete the row if the cell in column A is Blank, to as well delete the row if the cell in column A contains the string "Gender"?

我认为我需要更新:Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Application.ScreenUpdating = False
For Each ws In Worksheets 'and here
    Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    For Each MyCell In ws.Range("A2:EA2")
        If Not IsInArray(MyCell, arr) Then
            If HideMe Is Nothing Then
                Set HideMe = MyCell
            Else
                Set HideMe = Union(HideMe, MyCell)
            End If
        End If
    Next MyCell

    If Not HideMe Is Nothing Then
        HideMe.EntireColumn.Hidden = True
    End If

    Set HideMe = Nothing 'and here
Next ws 'and here
Application.ScreenUpdating = True    

推荐答案

这很简单:

1. 遍历每个工作表
2. 确定当前工作表中的最后一行(lr)
3. 遍历A列中的一系列单元格
4. 如果满足条件，则删除行

1. Loop through every worksheet
2. Determine last row (lr) in the current worksheet
3. Loop through a range of cells in column A
4. If the condition is met, delete the row

请注意，此^不是代码执行的顺序，而是代码的从上到下的解释

Option Explicit
Private Sub remove_blank_or_gender()

   Dim ws As Worksheet
   For Each ws In ThisWorkbook.Sheets

       Dim lr As Long 'last row
       lr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

       Dim i As Long
       For i = lr To 1 Step -1
           If IsEmpty(ws.Cells(i, 1)) Or ws.Cells(i, 1) = "Gender" Then
                ws.Rows(i).EntireRow.Delete
           End If
       Next i

    Next ws

End Sub

如果您有任何疑问，请告诉我.

If you have any questions, let me know.

此外，请避免将整个代码粘贴到您的 问题.您发布的代码应仅包含与问题相关的信息，如下所示: 最小，完整和可验证 示例

Also on a sidenote, please avoid pasting your entire code into your question. The code you post should only contain relevant information to the question as per: Minimal, Complete and Verifiable Example

这篇关于如何更新代码以删除空格，同时删除特定的“字符串"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！