The code, as written, assumes the worksheet you are searching is the active
sheet. You can either activate the worksheet before running the code or you
can preface the Range with the worksheet that is supposed to be search on
(doing this means you wouldn't have to activate the worksheet). To do the
latter, the code would look like this...
Application.FindFormat.Interior.ColorIndex = 15
On Error GoTo Done
Do
Worksheets("Sheet1").Range("E1:E" & Rows.Count).Find("", _
SearchFormat:=True).EntireRow.Delete xlShiftUp
Loop
Done:
where you would change my example Sheet1 worksheet name to the actual
worksheet name that your search is to take place on.
--
Rick (MVP - Excel)
"LiAD" <(E-Mail Removed)> wrote in message
news:93CA4C6D-0504-4036-B954-(E-Mail Removed)...
> Hi thanks for contining.
>
> I havent forgotten the other post. I had stopped for the day.
>
> The cells being deleted have numbers as well as the grey filling.
>
> The error I'm getting is Run-time error 91. Object variable or with block
> variable not set.
>
> The code is actually doing what is asked to do - it deletes all the greyed
> cells the problem is starting when it tries to do the loop. If i put a
> stop
> on loop, the code works fine before this and only gets this error when it
> tries to continue. When the error appears it highlights in yellow the
> same
> line as before, with or without the *.
>
>
>
> "Rick Rothstein" wrote:
>
>> First off, in case you haven't looked yet, I posted some code to the
>> other
>> thread we have been working on (Subject: Tough Teaser).
>>
>> Now, for this problem... sorry, I tested my code with values filled into
>> the
>> cells... I'm assuming your cells are empty (in the future, you should
>> tell
>> us what the error is, not just that you got one... it saves us from
>> guessing). The asterisk in the Find method is making it look for text.
>> The
>> way to just look for the color (whether there is text in the cell or not)
>> is
>> to remove the asterisk and just use the empty string "". Try it this
>> way...
>>
>> Application.FindFormat.Interior.ColorIndex = 15
>> On Error GoTo Done
>> Do
>> Range("E1:E" & Rows.Count).Find("", SearchFormat:=True). _
>> EntireRow.Delete xlShiftUp
>> Loop
>> Done:
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "LiAD" <(E-Mail Removed)> wrote in message
>> news:6A18E1BF-74EB-4A22-A877-(E-Mail Removed)...
>> > Thanks.
>> >
>> > I'm getting an error on this line
>> >
>> > Range("I3:I" & Rows.Count).Find("*",
>> > SearchFormat:=True).EntireRow.delete
>> > xlShiftUp
>> >
>> > Any ideas why this might be?
>> >
>> > "Rick Rothstein" wrote:
>> >
>> >> You will need a loop to do that. Assuming your "grey" shading is
>> >> ColorIndex
>> >> 15, and that the column you want to search in for this color is Column
>> >> E,
>> >> then this simple looping code should do what you want quite
>> >> efficiently...
>> >>
>> >> Application.FindFormat.Interior.ColorIndex = 15
>> >> On Error GoTo Done
>> >> Do
>> >> Range("E1:E" & Rows.Count).Find("*", SearchFormat:=True). _
>> >> EntireRow.Delete xlShiftUp
>> >> Loop
>> >> Done:
>> >>
>> >> --
>> >> Rick (MVP - Excel)
>> >>
>> >>
>> >> "LiAD" <(E-Mail Removed)> wrote in message
>> >> news:B552E47A-35BD-4847-809F-(E-Mail Removed)...
>> >> > Hi Rod,
>> >> >
>> >> > Thanks for your help once again.
>> >> >
>> >> > Is it possible to adapt the formula u just gave me, (below), in
>> >> > order
>> >> > to
>> >> > delete any cells in col I that have a grey shading?
>> >> >
>> >> > Range("E4:E" &
>> >> > Rows.Count).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
>> >> >
>> >> > Thanks
>> >>
>> >>
>>
>>
|
Similar Threads
