Deleting - Rick Rothstein

[LiAD]

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

 

[Patrick Molloy]

see my additional reply to your earleir mail

 

[Rick Rothstein]

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:

 

[LiAD]

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]

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:

 

[LiAD]

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]

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.