HELP Please! Need VBA code to Delete all Rows

[ktalone]

For some reason I can't get this code to work. I have multiple sheets within this workbook and multiple sections where "#REF!" is found with data in between - for this reason I was told to start from the bottom up on each worksheet. I would like the code to look to column A and find any cell with 'REF!' listed and delete the whole row. I am very, very new to VBA code so I googled and found this code and can't seem to get it to work with '#REF!'. Any help would be greatly appreciated!! thank you very much!!

Sub Delete_Rows()
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If Cells(i, "A").Value = "#REF!" Then
Cells(i, "A").EntireRow.Delete
End If
Next
End Sub


Katy

 

[DeBeuz]

Hi Katy,

Try 'IsError(Cells(i,"A").Value)' instead of 'Cells(i, "A").Value = "#REF!"'.
Also use 'Rows(i).EntireRow.Delete' instead of 'Cells(i, "A").EntireRow.Delete'.

Does this help?

Paul

 

[ktalone]

Actually it's just deleting the last row every time

 

[JoeMo]

Try this

Sub Delete_Rows()
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
on error resume next
With Range("A1:A" & LastRow).SpecialCells(xlCellTypeFormulas, xlErrors)
.Entirerow.delete
End with
On error goto 0
End Sub

 

[ktalone]

Figured it out! Just had to modify
Sub Delete_Rows()
'Deletes all rows with #REF! or error in column A
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If IsError(Cells(i, 1).Value) Then
Cells(i, 1).EntireRow.Delete
End If
Next
End Sub

 

[Smitty]

Welcome to the Board!

Instead of looping, I'd use AutoFilter. If you filter on #REF!, then you can use Goto-->Special-->Visible Cells only and then delete the selected rows. It's a lot faster. It's also recordable.

HTH,

 

[JoeMo]

The code I posted in Post #4 also avoids looping if you want to use code.

 

[Smitty]

The code I posted in Post #4 also avoids looping if you want to use code.

Sorry Joe, I didn't see your post.

 

[JoeMo]

Sorry Joe, I didn't see your post.

No apology needed Smitty, you provided a simpler non-VBA alternative. My post was just intended to give the OP a heads up!