Macro

[Rick Kalifa]

I'm trying to delete empty rows from my spreadsheet but I don't have a clue
how do it automatically. I like to write a macro that will go down a column,
cell by cell, and delete the empty rows. Any help on this would be greatly
appreciated.

Thanks,
RK.

 

[Bill James]

Some sample code... The trick is to start from last used
row.

Important: You must change "A" in Columns("A") to the
column in your worksheet that indicates if a row is empty.

Warning: If you run this code you cannot "Undo" the
delete!

Sub DeleteRowBasedOnEmptyCell()
Dim I As Long
Dim R As Range

Set R = ActiveSheet.UsedRange.Columns("A").Cells

For I = R.Count To 2 Step -1
If IsEmpty(R(I)) Then
ActiveSheet.Rows(I).Delete
End If
Next
End Sub

 

[Tushar Mehta]

You'll probably find many solutions in the google.com archives of the
xl newsgroups. One possible approach:

Option Explicit

Sub testIt()
Dim i As Long
With ActiveSheet.UsedRange
For i = .Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA( _
.Cells(i, 1).EntireRow) = 0 Then _
.Cells(i, 1).EntireRow.Delete
Next i
End With
End Sub


--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions