At my work we get Excel files with multiple worksheets that are pulled from various data sources. Some of the worksheets have a standardized disclaimer inserted at the end, some don't. But when the disclaimers appear they always start with the same text and always appear in the same column. I'm trying to write a VBA script that will search through an entire Excel file; determine if disclaimers are present, and if so, what row they start on; then clear all the cells from that row to the last used row.

As far as I can tell by hunting through StackOverflow and other resources, the code below should work. But for some reason, it never actually identifies when the key sub-string is present (even when it is). Can anyone point out where I am going wrong?

```
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

```