Delete Empty Rows

[christian]

Trying to write a macro that will read each row and if there is no dat
- delete it. Thought I could use the EntireRow.delete but not havin
any luck with it. (Excel 2002)

Now if only I could get the users to stop entering the blank rows...

Any help is appreciated.
-christia

 

[Norman Jones]

Hi Christian,

For rows with blanks in column A, Try:

Range("A1:A100").SpecialCells(xlCellTypeBlanks). _
EntireRow.Delete

Change the range to suit.

 

[Frank Kabel]

Hi
see:
http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows

 

[Ron de Bruin]

Hi Christian

Do you want to check one column or more ?
Post back if you want to check more columns

If you use Norman's example add the following line

On Error Resume Next 'In case there are no blank rows
Range("A1:A100").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

If you do this for a whole column be carfull with the SpecialCells limit
http://support.microsoft.com/default.aspx?scid=kb;en-us;832293

David have a example om his site to avoid problems
http://www.mvps.org/dmcritchie/excel/delempty.htm

The sub is named del_COLA_empty()



--
Regards Ron de Bruin
http://www.rondebruin.nl

Hi Christian,

For rows with blanks in column A, Try:

Range("A1:A100").SpecialCells(xlCellTypeBlanks). _
EntireRow.Delete

Change the range to suit.

 

[Tom Ogilvy]

lastrow = cells(rows.count,1).End(xlup).row
for i = lastrow to 1 step -1
if Application.CountA(rows(i)) = 0 then
rows(i).Delete
end if
Next