I need to delete 2 rows if a cell is blank.

At the moment I am using the following to delete the row which the blank
cell is situated in.

Range("C12:C81").SpecialCells(xlBlanks).EntireRow. Delete

This works fine. However, I also need to delete a second row relative to the
first row.

i.e. Cell C12 is blank
Row 12 is deleted
Row 112 is deleted

When deleting the first row (row 12) the second row to be deleted is now row
111.

This process is repeated through the range C12:C81. Obviously as more rows
are deleted, the relative row changes.

HELP - Any suggestions will be much appreciated.


Thanks in advance, Darren.

Try this code...

Sheet1.Range("C12:C81").SpecialCells(xlBlanks).Off set(100,
0).EntireRow.Delete
Sheet1.Range("C12:C81").SpecialCells(xlBlanks).Ent ireRow.Delete

It offsets from your blank cells to your other range and deletes the rows.
Make sure you run the offset first, otherwise you delet the blanks to offset
from.

HTH

"Darren Elsom" wrote:


I need to delete 2 rows if a cell is blank.

At the moment I am using the following to delete the row which the blank
cell is situated in.

Range("C12:C81").SpecialCells(xlBlanks).EntireRow. Delete

This works fine. However, I also need to delete a second row relative to the
first row.

i.e. Cell C12 is blank
Row 12 is deleted
Row 112 is deleted

When deleting the first row (row 12) the second row to be deleted is now row
111.

This process is repeated through the range C12:C81. Obviously as more rows
are deleted, the relative row changes.

HELP - Any suggestions will be much appreciated.


Thanks in advance, Darren.

The following seems to work for me:

With Range("C12:C81").SpecialCells(xlBlanks)
.Offset(100,0).EntireRow.Delete
.EntireRow.Delete
End With




On Fri, 4 Mar 2005 18:57:26 -0000, "Darren Elsom"
wrote:


I need to delete 2 rows if a cell is blank.

At the moment I am using the following to delete the row which the blank
cell is situated in.

Range("C12:C81").SpecialCells(xlBlanks).EntireRow .Delete

This works fine. However, I also need to delete a second row relative to the
first row.

i.e. Cell C12 is blank
Row 12 is deleted
Row 112 is deleted

When deleting the first row (row 12) the second row to be deleted is now row
111.

This process is repeated through the range C12:C81. Obviously as more rows
are deleted, the relative row changes.

HELP - Any suggestions will be much appreciated.


Thanks in advance, Darren.

Thankyou both. I have tried bothways within my existing code and they
both do they trick.

Thanks very much. Darren.