|First, a posting tip: |
Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Thanks!
As for your question, I would not loop through a spreadsheet using the COUNTIF function to determine if a specific string exists. That is very inefficient. Using the Find feature is much more efficient.
I'm also not sure why you are using Application.ScreenUpdating = True at the beginning of the code. Typically it is set to False to have the code run faster.
You didn't say which sheet or column you wanted to search so the following code assumes Sheet1 Column A. Modify as required.
Dim SrchRng As Range, c As Range
Dim firstAddress As String, lastRw As Long, dstRw As Long
Application.ScreenUpdating = False
'Search Data in Sheet1 Column A
With Sheets(1).Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
'Search for *off*, Copy entire Row for each occurrence to Sheet("To Do")
Set c = .Find("off", LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
dstRw = Sheets("To Do").Range("A" & Rows.Count).End(xlUp).Row + 1
c.EntireRow.Copy Sheets("To Do").Range("A" & dstRw)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Application.ScreenUpdating = True
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.