Delete entire row if A1,2,3... is empty

  • Thread starter Thread starter cimento cola
  • Start date Start date
C

cimento cola

Hey there.
I have this piece of code:

Private Sub CommandButton1_Click()
Dim i As Double
i = 1

For i = 1 To 5850
Cells(i, 1).Select
If ActiveCell.Value = Empty Then ActiveCell.EntireRow.Delete
i = i + 1
Next

End Sub

I want this:
In range A1:A5850 if cells in the first column (A) are empty then delete
entire row.
Exmple:
A
1 5
2
3 3
4 1

It should only delete the 2 entire row.
Can anyone help?
With that piece of code i get an I/O error and no rows deleted...
Thanks,
 
Try this
http://www.rondebruin.nl/delete.htm

One of this lines you can use

ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete
'This will delete each row if the cell is empty or have a formula that evaluates to "" ElseIf IsEmpty(.Cells(Lrow,
"A").Value) Then .Rows(Lrow).Delete
'This will delete the row if the cell is empty
 
This is better

ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete
'This will delete each row if the cell is empty or have a formula that evaluates to ""

ElseIf IsEmpty(.Cells(Lrow, "A").Value) Then .Rows(Lrow).Delete
'This will delete the row if the cell is empty
 
If there happened to be 2 blank spaces in a row, would it not skip the 2nd one?
The counting backwards seems to be the way to go.
For i = 1 To 5850
Cells(i, 1).Select
If IsEmpty(ActiveCell.Value) Then ActiveCell.EntireRow.Delete
Next i
Click to expand...

Mark.

Succes;

More Excel ? www.rosenkrantz.nl or (e-mail address removed)
 
Back
Top