Removing blank rows

[Noir]

My spreadsheet has 2 columns of data. Column A contains part numbers and column B contains serial numbers. I frequently delete part numbers (and serial numbers) due to part discontinuations, etc which leaves many blank rows within my spreadsheet. Removing these rows individually can get time consuming (I sometimes have 40 or 50 rows of data sparatically located throughout my spreadsheet).

How can i remove the blank rows all at once?

Thx,
Noir

[Brian from Maui]

Try sorting the columns

[Eddie G.]

this works well...........

Sub DeleteEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 _
Then Rows(r).Delete
Next r
End Sub

[Noir]

Eddie,
Sorry but, i could not get your code to work. Please help.

Noir

[Bukol]

Noir,
someone send me a little add-in that does just what you need.
Give me your e-mail, I'll send it to you
Hans

[nisht]

Friend,

why dont you sort the rows...

you will have all your blank rows at the end.

nishith desai
http://www.pexcel.com

[Asala42]

Hiya,

Seriously I'd go with Nisht and Brian's recommendations- quick and easy. If the original order of the list is needed you could add a column of sequential numbers before sorting - then resort the list by the new column after deleting the rows with no data.

Hope that helps,
Adam

[Noir]

Thanks for your help guys!! The sorting and the codes worked.

Noir