Excel Macro Help: deleting empty rows?
I'm trying to delete empty rows, but my macro is too slow.
Sub delete_test()
For i = 1 To 10000
If Range("A" & i) = "" Then
Range("A" & i).EntireRow.Delete
End If
Next i
End Sub
I try to use .select all empty row, then delete later, but it kept on selecting and unselecting.
Sub delete_test()
For i = 1 To 10000
If Range("A" & i) = "" Then
Range("A" & i).EntireRow.Select
End If
Next i
Selection.Delete
End Sub
Please Help!
More Questions From This User See All
Answers & Comments
Verified answer
Selecting the row will slow down the process. Don't select. Just go for delete.
However this may not delete all rows where column A is "". (If there are two consecutive blanks)
Try this code
Sub macro1()
For i = 100 To 1 Step -1
If Cells(i, 1) = "" Then
Rows(i).EntireRow.Delete
End If
Next i
End Sub
Sub DeleteEmptyRows() Dim Counter Dim i As Integer Range("A1").Select Counter = InputBox("Enter the total number of rows to process") ActiveCell.Select For i = 1 To Counter If ActiveCell = "" Then Selection.EntireRow.Delete Counter = Counter - 1 Else ActiveCell.Offset(1, 0).Select End If Next i End Sub
Could try this...assuming an empty row is defined by an empty cell in column A.
All one line
Range("A:A")
.SpecialCells
(xlCellTypeBlanks)
.EntireRow.Delete
Try this:
For i = 1 to 10000
ActiveSheet.Rows(i).Delete
Next
The Data Analyst - http://www.squidoo.com/thedataanalyst