Delete blank rows in a table or in a range

[luolovepi]

Does anyone can teach me how to modify the code below to allow me to delete blank rows in table "Table1" and range "Purpose"? I want to delete those blank rows from the last row until reaching a non-empty row.




</PRE>

Is it possible? Thank you!




</PRE>

Sub DelEmptyRows()  
Dim i As Long, iLimit As Long    
iLimit = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False  
Application.Calculation = xlCalculationManual    'pre XL97 xlManual  
For i = iLimit To 1 Step -1    
If Application.CountA(Cells(i, 1).EntireRow) = 0 Then       
Cells(i, 1).EntireRow.Delete    
End If  
Next i
Application.Calculation = xlCalculationAutomatic   
Application.ScreenUpdating = True   
iLimit = ActiveSheet.UsedRange.Rows.Count   'attempt to fix lastcell   
ActiveWorkbook.Save
End Sub

Best regards,
lolo^^




</PRE>

 

[mirabeau]

Not knowing how your range "purpose" is defined I used the entire worksheet.

If your range "purpose" is actually (say) Range("A3:D46") then replace "Cells"in the 3rd line by Range("A3:D46").

Sub delblrow()
Dim purpose As Range, i&, ilimit&, c&
Set purpose = Cells
ilimit = purpose.Find("*", after:=purpose(1), searchorder:=xlByRows, _
    searchdirection:=xlPrevious).Row
c = purpose(1).Row
For i = ilimit To c Step -1
If Application.CountA(purpose.Rows(i - c)) = 0 Then
    purpose.Rows(i - c).Delete xlUp
Else
    Exit For
End If
Next i
End Sub