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

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

match vba excel

-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 年前

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 .

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

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