Macro to delete blank rows in a data range

[Youlan]

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.

 

[Gary''s Student]

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

 

[Per Jessen]

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

 

[Lori]

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

 

[Gord Dibben]

Without a macro...........Select a column and
F5>Special>Blanks>OK>Edit>Delete>Entire Rows


Gord Dibben MS Excel MVP

 

[Diana]

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

Diana Vazian