Delete blank rows

  • Thread starter Thread starter Andy B
  • Start date Start date
A

Andy B

Hi all

After having imported a .prn file, I have sorted the data so that what I
need is at the top. I now have a couple of hundred lines of data that I
require and beyond that I have the headers, footers etc that I don't want.
As this will become a regular job, I would like a macro that will start in
cell E4, continue down the column, and when it finds a blank cell it deletes
the rest of the rows below.
Thanks in advance!!

Andy.
 
Try this piece of code in your macro:

Range("E4").Select
Selection.End(xlDown).Select
rw1 = ActiveCell.Row
ActiveCell.SpecialCells(xlLastCell).Select
rw2 = ActiveCell.Row
Rows(ActiveCell.Row).Select
Range(Selection, Selection.Offset(-rw2 + rw1 + 1,
0)).Select
Selection.Delete shift:=xlUp

You can use F8 (step into) in the VB window to see how it
actually works. The idea is to locate the last populated
cell in column E, store the row number in variable rw1,
the locate the last "used" cell in the sheet, store its
row number in rw2, select the rows between (rw1 + 1) and
rw2 and delete them.

Nikos
 
Back
Top