使用VBA识别Excel工作表中包含文本子字符串的特定行

在我的工作中，我们获得具有多个工作表的Excel文件，这些工作表是从各种数据源提取的。有些工作表的末尾插入了标准的免责声明，有些则没有。但是，当出现免责声明时，它们总是以相同的文本开头，并始终出现在同一列中。我正在尝试编写一个VBA脚本，它将搜索整个Excel文件。确定是否存在免责声明，以及是否存在免责声明；然后清除该行到最后使用的行的所有单元格。

就我通过遍历StackOverflow和其他资源所知，下面的代码应该可以工作。但是由于某种原因，它实际上无法识别出何时存在键子字符串（即使存在）。谁能指出我要去哪里错了？

Option Explicit

Option Base 1

Sub Delete_Disclaimers()

' Turn off screen updating for speed

Application.ScreenUpdating = False


' Define variables

Dim ws As Worksheet
Dim TextCheck As String
Dim StartRow As Integer
Dim EndRow As Integer
Dim SearchColumn As Integer
Dim CheckVal As Integer
Dim CurrentCell As Range
Dim RowCount As Integer
Dim SearchText As String


' Cycle through each worksheet in the workbook
For Each ws In ActiveWorkbook.Worksheets


'Set some initial variables for this worksheet

SearchColumn = 2
StartRow = 1
SearchText = "Disclaimer"

' Set EndRow to the last row used in the worksheet
EndRow = CInt(ws.UsedRange.SpecialCells(xlCellTypeLastCell).Row)


' Find the cell, if any, that has the text by searching just in column B to speed things up.  Also limit to the first 200 rows
' for speed since there don't seem to have any sheets longer than that.    

For RowCount = 1 To 200
    Set CurrentCell = ws.Cells(2, RowCount)
    TextCheck = CurrentCell.Text
    If Not TextCheck = "" Then
        CheckVal = InStr(1, TextCheck, SearchText, 1)
        If CheckVal > 0 Then
            StartRow = RowCount
            MsgBox ("Start Row is " & CStr(StartRow))
            Exit For
        End If
    End If
Next RowCount


' If the search text was found, clear the range from the start row to the end row. 

If StartRow > 1 Then
    ws.Range(ws.Cells(1, StartRow), ws.Cells(50, EndRow)).Clear
End If



' Loops to next Worksheet
Next ws


' Turn screen updating back on
Application.ScreenUpdating = True


' Display a message box that all sheets have been cleared, now that screenupdating is back on
MsgBox "All Worksheets have been cleared!"


End Sub