查找特定文本并删除其上方的三行 [英] Find specific text and delete three rows above it

问题描述

我在使用宏时遇到了一些麻烦，我想再次向宏专家咨询一下.

I'm having some trouble with my macros, and I thought that I'd ask the macro pros on here again for some assistance.

我需要帮助在一行中定位某些文本并删除其上方的三行，并且不会循环.这是我一直在使用的，并且没有用.图片:我需要删除的内容和所需的最终结果.还有我正在使用的宏

I need help locating certain text in a row and deleting three rows above it, unlooped. This is what I've been using and it's not working. Image of: What I need deleted and the required end result. Also the Macros that I'm using

Sub Delete_Rows()

Dim x
For x = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row To ActiveCell.Row Step -1
    If Cells(x, 1) = "TOT" Then 
        Cells(x, 1).EntireRow.Delete      
        Cells(x - 1, 1).EntireRow.Delete  
        Cells(x - 2, 1).EntireRow.Delete  
        Cells(x - 3, 1).EntireRow.Delete  
End If

End Sub

我也在线上寻找了针对此常见问题的其他解决方案，但对我而言没有任何用处.我需要此宏在Excel工作簿的任何页面中都可用.

I've also looked around online for other solutions to this common problem, but nothing worked for me. I need for this macro to be usable in any page of the Excel Work book.

非常感谢大家！

推荐答案

如果我正确理解，下面的编辑应该会帮助

If i understand correctly, the below edit should help

Sub Delete_Rows()

Dim xRow As Integer
Dim strSearch As String

strSearch = "TOT"
' Assuming Total is in column C as your picture shows, but you can configure to search anywhere

xRow = Range("C" & Rows.Count).End(xlUp).Row
Range("$C1:C" & xRow).Select

Selection.Find(What:=strSearch, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

Range(ActiveCell.Row & ":" & ActiveCell.Offset(-3, 0).Row).Select
Selection.Delete Shift:=xlUp

End Sub

这篇关于查找特定文本并删除其上方的三行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！