Hi

Thanx for replying me in time for my earlier question.

I have a data of 15000 rows. My problem is there are spaces of a row or two
between each records. I could have sort it to remove the blank rows in
between. But do not want to change the order of the records. pls suggest
the right way to remove blank rows from the records in short period of time.

thanx in advance.
Manu

Hi,

I have assumed that if the cell in column A is empty then this identifies a
blank row. If that is correct then right click the sheet tab, view code and
paste this in:-

Sub surface()
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For x = LastRow To 1 Step -1
Cells(x, 1).Select
If Cells(x, 1).Value = "" Then
Selection.EntireRow.Delete
End If
Next
End Sub

Mike


"manu" wrote:

> Hi
>
> Thanx for replying me in time for my earlier question.
>
> I have a data of 15000 rows. My problem is there are spaces of a row or two
> between each records. I could have sort it to remove the blank rows in
> between. But do not want to change the order of the records. pls suggest
> the right way to remove blank rows from the records in short period of time.
>
> thanx in advance.
> Manu

Its working.

Thanx a lot.

rgds,
Manu

"Mike H" wrote:

> Hi,
>
> I have assumed that if the cell in column A is empty then this identifies a
> blank row. If that is correct then right click the sheet tab, view code and
> paste this in:-
>
> Sub surface()
> With ActiveSheet
> LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
> End With
>
> For x = LastRow To 1 Step -1
> Cells(x, 1).Select
> If Cells(x, 1).Value = "" Then
> Selection.EntireRow.Delete
> End If
> Next
> End Sub
>
> Mike
>
>
> "manu" wrote:
>
> > Hi
> >
> > Thanx for replying me in time for my earlier question.
> >
> > I have a data of 15000 rows. My problem is there are spaces of a row or two
> > between each records. I could have sort it to remove the blank rows in
> > between. But do not want to change the order of the records. pls suggest
> > the right way to remove blank rows from the records in short period of time.
> >
> > thanx in advance.
> > Manu

Personally, I would use:

Sub surface()

Dim rng As Range

Set rng = Application.Intersect(Cells.Columns(1), ActiveSheet.UsedRange)

On Error Resume Next
rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

End Sub


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Mike H" wrote:

> Hi,
>
> I have assumed that if the cell in column A is empty then this identifies a
> blank row. If that is correct then right click the sheet tab, view code and
> paste this in:-
>
> Sub surface()
> With ActiveSheet
> LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
> End With
>
> For x = LastRow To 1 Step -1
> Cells(x, 1).Select
> If Cells(x, 1).Value = "" Then
> Selection.EntireRow.Delete
> End If
> Next
> End Sub
>
> Mike

Select a column and F5>Special>Blanks>OK

Edit>Delete>Entire Row.


Gord Dibben MS Excel MVP

On Mon, 9 Jul 2007 01:12:07 -0700, manu <(E-Mail Removed)> wrote:

>Hi
>
>Thanx for replying me in time for my earlier question.
>
>I have a data of 15000 rows. My problem is there are spaces of a row or two
>between each records. I could have sort it to remove the blank rows in
>between. But do not want to change the order of the records. pls suggest
>the right way to remove blank rows from the records in short period of time.
>
>thanx in advance.
>Manu