Macro to delete blank rows in a data range
 

Hi,

I'm using Excel 2002 and I'm working in a spreasheet with data copied from
an external source. I need to automatically delete the blank rows without
having to go through the entire document. Also their is no set format to when
and how many blank rows are in the document.

Can anyone help?

Thanks in advance.

Macro to delete blank rows in a data range
 

try:

Sub macro_der()
Dim i As Long, nLastRow As Long
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
Set r = Rows(nLastRow + 1)
For i = 1 To nLastRow
If Application.CountA(Rows(i)) = 0 Then
Set r = Union(r, Rows(i))
End If
Next
r.Delete
End Sub

--
Gary''s Student - gsnu200776


"Youlan" wrote:

Hi,

I'm using Excel 2002 and I'm working in a spreasheet with data copied from
an external source. I need to automatically delete the blank rows without
having to go through the entire document. Also their is no set format to when
and how many blank rows are in the document.

Can anyone help?

Thanks in advance.

Macro to delete blank rows in a data range
 

Hi

This routine will look at cells in column A and delete entire row if the
cell is empty.

Sub RemoveBlankRows()
Dim TargetRange As Range
Const TargetColumn As Integer = 1 'Check fo empty cells in coloumn A
Set TargetRange = Range(Cells(1, TargetColumn), Cells(65536,
TargetColumn).End(xlUp))
rowcount = TargetRange.Rows.Count
For r = rowcount To 1 Step -1
If Cells(r, 1).Value = "" Then Rows(r).Delete
Next
End Sub

Regards,

Per

"Youlan" skrev i meddelelsen
...
Hi,

I'm using Excel 2002 and I'm working in a spreasheet with data copied from
an external source. I need to automatically delete the blank rows without
having to go through the entire document. Also their is no set format to
when
and how many blank rows are in the document.

Can anyone help?

Thanks in advance.

Macro to delete blank rows in a data range
 

Sub DeleteBlankRows()
Cells.EntireRow.Hidden=False
Cells.ColumnDifferences(Cells(Cells.Count)).Entire Row.Hidden=True
Cells.SpecialCells(xlCellTypeVisible).Delete
Cells.EntireRow.Hidden=False
End Sub

"Youlan" wrote:

Hi,

I'm using Excel 2002 and I'm working in a spreasheet with data copied from
an external source. I need to automatically delete the blank rows without
having to go through the entire document. Also their is no set format to when
and how many blank rows are in the document.

Can anyone help?

Thanks in advance.

Macro to delete blank rows in a data range
 

Without a macro...........Select a column and
F5SpecialBlanksOKEditDeleteEntire Rows


Gord Dibben MS Excel MVP

On Fri, 28 Mar 2008 08:17:01 -0700, Youlan
wrote:

Hi,

I'm using Excel 2002 and I'm working in a spreasheet with data copied from
an external source. I need to automatically delete the blank rows without
having to go through the entire document. Also their is no set format to when
and how many blank rows are in the document.

Can anyone help?

Thanks in advance.

Macro to delete blank rows in a data range
 

Thank you very much, it"s nice and simple and was very helpful to me.

Diana Vazian

"Gord Dibben" wrote:

Without a macro...........Select a column and
F5SpecialBlanksOKEditDeleteEntire Rows


Gord Dibben MS Excel MVP

On Fri, 28 Mar 2008 08:17:01 -0700, Youlan
wrote:

Hi,

I'm using Excel 2002 and I'm working in a spreasheet with data copied from
an external source. I need to automatically delete the blank rows without
having to go through the entire document. Also their is no set format to when
and how many blank rows are in the document.

Can anyone help?

Thanks in advance.