delete row is A(x) is blank

[lijon]

Please help... I'm frustrated and give up! I'm trying to make a loop that should start from bottom to top and delete a row if there's no value in Row(?) ColumnA. Here's what I have so far, but it's not working. HELP!

It's the DELETE part that doesn't work... thanks to all.



For cds = 460 To 452 Step -1
Range("a" & cds).Select

If Range("A" & cds).Value = "" Then Rows("CDS:cds").Delete

Next cds

[Demosthine]

<-- Error In Post -->

[Demosthine]

Hey there Lijon.

You are receiving an error because your Rows("CDS:cds").Delete statement is not referencing a valid row to delete. By placing the quotation marks around it, it is looking for a literal CDS, just like it would A1. You need to specify a valid range just like you did the Range("a" & cds).Select statement. Try the following code.


For cds = 460 To 452 Step -1
With Range("A" & cds)
If .Value = "" Then _
.EntireRow.Delete
End With
Next cds


Scott

[Bob Phillips]

Without looping



Range("A452:A460").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

[rachelka]

Hi

you can use this.

(I work on Excel 2003).

Private Sub deleteemptyrows()

For i = Cells(65536, "A").End(xlUp).Row To 1 Step -1
If Cells(i, "A").Value = "" Then
Rows(i).Delete Shift:=xlUp
End If
Next

End Sub

It will check all cells in "A" columns and delete empty rows.
Of course you can change this column if you want. :thumb

To make it quicker you can add.


Private Sub deleteemptyrows()

Application.ScreenUpdating = False
EnableEvents = False

For i = Cells(65536, "A").End(xlUp).Row To 1 Step -1
If Cells(i, "A").Value = "" Then
Rows(i).Delete Shift:=xlUp
End If
Next

Application.ScreenUpdating = True
EnableEvents = True
End Sub