Macro to delete blank rows in a data range

Y

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.
 
G

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
 
P

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
 
L

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
 
G

Gord Dibben

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


Gord Dibben MS Excel MVP
 
Top