How to detect and delete empty rows in a list?

[Guest]

I am preparing Excel lists I get from external sources to be used as databases. Some of them contain empty rows. How do I detect and delete those rows programmatically

Would be very pleased if somebody could help me with this proble
Steff

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows

--
Regards
Frank Kabel
Frankfurt, Germany

I am preparing Excel lists I get from external sources to be used as

databases. Some of them contain empty rows. How do I detect and delete
those rows programmatically?

 

[Kalle]

Set YourSheet = Worksheets("YourSheetnmae")

Do Until IsEmpty( YourSheet.Cells(i + 1, aColumn).Value)
If isempty(YourSheet.Cells(i + 1, aColumn).Value) Then
YourSheet.Cells(i + 1, aColumn).Select
Selection.Delete Shift:=xlUp
End If
i = i + 1
Loop

 

[Nikos Yannacopoulos]

Steff,

Why don't you just sort?

Nikos

I am preparing Excel lists I get from external sources to be used as

databases. Some of them contain empty rows. How do I detect and delete those
rows programmatically?

 

[Guest]

Thanks all of you
Pearson's solution works fine, but unfortunately some of the seemingly empty cells in a seemingly empty row have been formatted e.g as numbers and are then not detected as empty

Kalles suggestion does not work with m

Nikos suggestion to sort has the disadvantage that the original sorting order is change

My own solution, finally, was to filter out all the values of a specific column that are >0

 

[Nikos Yannacopoulos]

Steff,

In order to kee the original sorting:

* Use an extra column to add a serial number from the first data row down
(1,2,3, etc)
* Sort on the data itself and delete the blank rows (they are all at the top
or bottom of the data)
* Re-sort on the serial numbers, and you get your original sorting back! Now
you can also delete the S/N column.

HTH,
Nikos

Thanks all of you:
Pearson's solution works fine, but unfortunately some of the seemingly

empty cells in a seemingly empty row have been formatted e.g as numbers and
are then not detected as empty.

Kalles suggestion does not work with me

Nikos suggestion to sort has the disadvantage that the original sorting order is changed

My own solution, finally, was to filter out all the values of a specific

column that are >0

 

[Tom Ogilvy]

Wouldn't your code just stop when it hit the first empty cell, thus
appearing to do nothing.