代码之家  ›  专栏  ›  技术社区  ›  Tuna

如何删除最后一行包含指定字符串(最后一个匹配项)下面的行?通过VBA

  •  -1
  • Tuna  · 技术社区  · 6 年前

    您好,我正在搜索并尝试合并一些VBA代码以克服我的问题,但迄今为止尚未成功。我要做的是搜索列中的特定单词(即“XxX”这只是该单元格的一部分)并找到最后一个匹配项,然后使用VBA删除此行下方的行。我有278个txt文件需要转换成xlsx,以便批量应用。我加了一个例子,也许我会解释得更好。

    Ağ Teknolojileri Elemanı
    Network Technologies Personnel
    (Level 5)
    12UY0046-5/A1
    Occupational Health and Safety, Quality, Work Organization and Professional Development
    XxX Explains OHS measures.
    XxX Explains environment protection measures.
    XxX Defines quality applications.
    XxX Explains how to make work organization.
    XxX Defines activities needed to be carried out for professional development.
    12UY0046-5/A2
    Basis of Computer Hardware and Software
    XxX Explains the operating logic of the computer.
    XxX Explains basic computer use.
    XxX Explains the working principles of computer energy hardwares.
    XxX Describes the basic features of computer components.
    df
    fsd
    s
    gfd
    gdfg
    dfs
    fd
    

    对于此示例,我希望VBA代码将找到包含“XxX”(XxX描述了计算机组件的基本功能)的最后一个单元格,并删除此行下面的所有行。

    我不知道我是否可以对txt文件执行此操作。但尽管如此,我还是找不到一种方法来处理txt文件,我需要将这些txt文件更改为xlsx。

    2 回复  |  直到 6 年前
        1
  •  0
  •   Ron Rosenfeld    6 年前

    尝试:

    Option Explicit
    Sub delAfter()
        Const sFind As String = "XxX"
        Dim WS As Worksheet, R As Range
    
    'Find last sFind
    Set WS = Worksheets("sheet2") 'or ActiveSheet or whatever
    With WS.Cells
        Set R = .Find(what:=sFind, _
             after:=.Item(1, 1), _
             LookIn:=xlValues, _
             lookat:=xlPart, _
             searchorder:=xlByRows, _
             searchdirection:=xlPrevious, _
             MatchCase:=True)
        If Not R Is Nothing Then
            Set R = WS.Range(R.Offset(1, 0), .Cells(.Rows.Count, R.Column).End(xlUp))
            R.EntireRow.Delete 'deletes entire row.  If just want a single column, adjust
        End If
    End With
    
    End Sub
    

    将在查找最后一行后删除整行 XxX .

    如果需要将其限制为特定列,只需更改正在搜索的范围即可。

        2
  •  0
  •   Lambik    6 年前

    Option Explicit
    
    Sub Delete()
    
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim Lastrow As Long, i As Long, RowNum As Long
    Dim str As String
    Dim r As Range, Cell As Range
    
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Blad1")
    
    str = "XxX"
    
    Lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set r = ws.Range(ws.Cells(1, 1), ws.Cells(Lastrow, 1))
    
    For Each Cell In r
        If Cell.Value Like "*" & str & "*" Then
            i = Cell.Row
            Exit For
        End If
    Next Cell
    
    For RowNum = i + 1 To Lastrow
        ws.Rows(i + 1).Delete
    Next RowNum
    
    
    End Sub