Deleting rows if cell is blank

  • Thread starter Thread starter Darren Elsom
  • Start date Start date
D

Darren Elsom

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).Offset(100,
0).EntireRow.Delete
Sheet1.Range("C12:C81").SpecialCells(xlBlanks).EntireRow.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
 
The following seems to work for me:

With Range("C12:C81").SpecialCells(xlBlanks)
.Offset(100,0).EntireRow.Delete
.EntireRow.Delete
End With
 
Thankyou both. I have tried bothways within my existing code and they
both do they trick.

Thanks very much. Darren.
 
Back
Top