Delete Empty Rows Macro

  • Thread starter Thread starter yepiknowiam
  • Start date Start date
Y

yepiknowiam

I have been looking for a macro that will delete any empty row. This
spreadsheet I work with generally has 3-4 empty rows after each section.

It doesn't have to be complex.

Thanks.
 
Sub DeleteEmptyRows()
''only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then
Rows(R).Delete
End If
Next R
End Sub


Gord Dibben MS Excel MVP
 
Hi,

The following macro should help. Take note of the comment re the column.

Ensure that you backup your workbook in case it does not do exactly what you
want.

Sub Delete_Rows()

Dim rngA As Range
Dim c As Range
Dim i As Long
Dim colCount As Long

'Assumes that column A will have data in the last cell
'within the range being tested. Otherwise change "A"
'to a column that does have data in last cell of total
'range being tested.

Set rngA = ActiveSheet.Range(Cells(1, "A"), _
Cells(Rows.Count, "A").End(xlUp))

colCount = ActiveSheet.Columns.Count

'Work from bottom when deleting rows
With rngA
For i = .Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Range(Cells(i, 1), _
Cells(i, colCount))) = 0 Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
End Sub
 
Everything works fine, but have another question. If I open up a different
workbook, the macro is not listed. Is there a way to save it or import it so
any workbook I open up, I will have access to it?
 
Gord's reply is much better. It's completely generic. We posted
simultaneously but had I seen his reply first I would not have posted mine.
 
Lots of people who have this kind of useful macro create a workbook named
personal.xls (.xlsm). Then they store that workbook in their XLStart folder.

That way, excel will open this macro workbook whenever excel opens.

In fact, most people have this personal.xls hidden -- so it doesn't get in the
way of real work.

Then you can just open any workbook,
hit alt-f8 (tools|macro|macros in xl2003 menus)
select the macro and run it.
 
Back
Top