根据条件删除行 [英] delete row based on condition

问题描述

我希望有人可以帮助这段代码。我试图删除整行，如果我在列A中找到文本'FemImplant'。棘手的部分是这个文本是由'$'链接的句子的一部分。所以我需要代码解析出'$'之前的单元格内容，看看它是否符合'FemImplant'并删除该行。这是我到目前为止，但它是nto工作。

  Dim cell As Excel.Range 
 RowCount = DataSheet .UsedRange.Rows.Count 
 Set col = DataSheet.Range（A1：A& RowCount）
 Dim SheetName As String 
 Dim ParsedCell（）As String 
 
对于每个单元格在col 
 
 ParsedCell = cell.Value.Split（$）
 SheetName = ParsedCell（0）
 
如果SheetName =FemImplant 然后
 cell.EntireRow.Delete Shift：= xlUp 
 
如果
  

Next

解决方案

您可以使用AutoFilter删除包含文本 FemImplant $ 。这个方法比循环要快得多。

看到这个例子

我假设单元格A1有标题。

  Sub Sample（）
 Dim ws As Worksheet 
 Dim strSearch As String 
 Dim lRow As Long 
 
 strSearch =FemImplant $
 
设置ws =表格（Sheet1）
 
带有ws 
 lRow = .Range（A& .Rows.Count）.End（xlUp）.Row 
 
'~~>删除任何过滤器
 .AutoFilterMode = False 
 
'~~>使用.Range（A1：A& lRow）
 .AutoFilter字段：= 1，Criteria1：== *& strSearch& *
 .Offset（1，0）.SpecialCells（xlCellTypeVisible）.EntireRow.Delete 
 End with 
 
'~~>删除任何过滤器
 .AutoFilterMode = False 
结束
 End Sub 
  

SNAPSHOT

I was hoping someone could help with this piece of code. I wam trying to delete the entire row if I find the text 'FemImplant' in column A. The tricky part is that this text is part of a sentence linked by '$'. So I need the code to parse out the cell content before '$' and see if it matches 'FemImplant' and delete that row. This is what I have so far but it is nto working.

Dim cell As Excel.Range
 RowCount = DataSheet.UsedRange.Rows.Count
 Set col = DataSheet.Range("A1:A" & RowCount)
 Dim SheetName As String
 Dim ParsedCell() As String

 For Each cell In col

 ParsedCell = cell.Value.Split("$")
 SheetName = ParsedCell(0)

 If SheetName = "FemImplant" Then
 cell.EntireRow.Delete Shift:=xlUp

 End If

Next

解决方案

You can use AutoFilter to delete the rows which contain the text FemImplant$. This method will be much faster than looping.

See this example

I am assuming that Cell A1 has header.

Sub Sample()
    Dim ws As Worksheet
    Dim strSearch As String
    Dim lRow As Long

    strSearch = "FemImplant$"

    Set ws = Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Remove any filters
        .AutoFilterMode = False

        '~~> Filter, offset(to exclude headers) and delete visible rows
        With .Range("A1:A" & lRow)
          .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
          .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

SNAPSHOT

这篇关于根据条件删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！