How to Delete Range ?

  • Thread starter Thread starter slappy54
  • Start date Start date
S

slappy54

What I have works, but it's pretty slow. It selects the first blank cell in
a column and deletes the next fifteen rows. Is there a better way of
deleting those 15 rows?

Range("D2").Select
Selection.End(xlDown).Offset(1, 0).Select
For I = 1 To 15
ActiveCell.Offset(1, 0).EntireRow.Delete
Next I

Thanks,
Mike
 
range("d2").end(xldown).offset(2,0).resize(15).entirerow.delete

You're skipping one blank row and then deleting the next 15????
 
I didn't know I was skipping one blank row. I guess I need to delete the
next sixteen rows from the last value in the d column.

I appreciate your help.
Mike
 
The reason you are skipping one, is because your first line

Selection.End(xlDown).Offset(1, 0).Select

selects the first blank cell as a result of using Offset, BUT, you then go and repeat that offset
in your next line:-

ActiveCell.Offset(1, 0).EntireRow.Delete

which will delete the line that is offset 1 row from the blank cell you have already selected (ie
the blank row selected in the first line of code). This means that you have in effect, offset
twice, and as Dave said, skipped the first blank row.

Just decide how many blank rows you need to delete after your last piece of data, whether or not
you want to delete the first blank line after your data as well, and then adjust Dave's line of
code:-

If you do want to delete that first blank line, then make the offset 1, if you don't make the
offset 2. Then just amend the 15 to be the actual number of rows you want to delete in total.

range("d2").end(xldown).offset(1,0).resize(15).entirerow.delete will delete 15 rows from and
including the first blank line after your data.

range("d2").end(xldown).offset(1,0).resize(16).entirerow.delete will delete 16 rows from and
including the first blank line after your data.

range("d2").end(xldown).offset(2,0).resize(15).entirerow.delete will delete 15 rows from and
including the line AFTER the first blank line in your data.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



slappy54 said:
I didn't know I was skipping one blank row. I guess I need to delete the
next sixteen rows from the last value in the d column.

I appreciate your help.
Mike
Click to expand...
 
Buried in Ken's response (vvbg), he had this:

range("d2").end(xldown).offset(1,0).resize(16).entirerow.delete

It'll find the bottom of column D, come down one and delete it and the next 15,
too.

Another option that I like to use to get to the bottom of the column is start at
the bottom and work up. I like this so I don't have to worry about empty cells
in the middle of the data:

range("d65536").end(xlup).offset(1,0).resize(16).entirerow.delete

(but there's no difference if your data in column D has no gaps.)
 
Back
Top