[Excel] Office 2007 VB Script for slecting rows

[abitdodgy]

Hello,

I am currently using the following code to select rows where a cell contains a specific value. For example:

Dim d As Range
   Dim rngD As Range
   For Each d In Intersect(ActiveSheet.UsedRange, Columns("F"))
       If d = "Canceled" Then
           If rngD Is Nothing Then Set rngD = d.EntireRow
           Set rngD = Union(rngD, d.EntireRow)
       End If
   Next d
   rngD.Select
   Selection.Delete

The above code looks for the word canceled and deletes any rows where "canceled" is found in column F.

However, when "canceled" is not in any cell in column F, i get a "Runtime error '91': Object variable or With block variable is not set"

Is there a way around this? I'm a complete novice to VB and generally to most types of programing.

Many thanks!

[Ecniv]

use your range object and its built in excel function .find

If you grab the current address and then use it, the object moves to the cell that it finds it in.

Doesn't cancelled have two l's?

Alternatively, with no code you should be able to flag all rows that have cancelled in a particular cell :-> =IF(ISERROR(FIND("cancelled",B2)),"N","Y")
note that this is case sensative...