deleting rows that don't have data

[ann]

I have a very long column of numbers (about 43,000 rows),
however there is a blank row between each number. I want
to delete each row that does not contain the numbers.
Instead of clicking on each empty row and deleting each
row, is there a faster way to complete?

 

[Don Guillett]

sort & delete

 

[Kevin Stecyk]

Hi Ann,

Here is a solution provided by Tom Olgivy....

Sub DeleteRows()
Dim rnRange As Range

Set rnRange = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
Set rnRange = rnRange.SpecialCells(xlBlanks)
rnRange.EntireRow.Delete
End Sub

This was provided by Olgivy.

Steps:

1) Press Alt F11
2) Insert Module
3) Copy and Paste above code into module
4) Alt F8 ...select Delete Rows

Hope this helps.

Regards,
Kevin