Deleting Unwanted Rows

Crossman

New Member
Joined
Feb 24, 2013
Messages
10
Hi,

I am having a small problem with a simple code and I was wondering if someone could explain to me what I'm missing.


Essentially I have imported some data and run some formulas. I was being a bit lazy and ran the formulas down over a defined number of rows instead of inserting only into rows that had data (the number of rows is variable depending on the data). The result is a bunch of rows at the end with formulas that return erros such as #VALUE!. I wanted to find these rows and delete the entire row using the below code.

The code works but it seems to delete only every other row. I assume this has something to do with the delete method shifting the rows up each time. I am still learning (teaching myself) VBA so can someone please explain what's going on? Much appreciated.:confused:

Sub DeleteRow()
Dim Cell As Variant

Range("A:A").Select
For Each Cell In Selection
If IsError(Cell) Then
Cell.Offset(1).EntireRow.Delete


End If
Next Cell

End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
You are correct about the rows shifting up and some would get skipped over. The solution is to loop from the bottom up.

Code:
Sub DeleteRow()
    Dim i As Long, Lastrow As Long
    
    Lastrow = Range("A" & Rows.Count).End(xlUp).Row
    For i = Lastrow To 1 Step -1    'Loop from bottom up
        If IsError(Range("A" & i)) Then Rows(i).Delete
    Next i
    
End Sub


Or even better...
Code:
Sub Delete_Formulas_with_Errors()
    
    Range("A:A").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
    
End Sub
 
Upvote 0
When you delete with looping you need to delete from bottom to top but in this case I would avoid looping altogether.
Try the code below on a copy of your data.
Code:
Sub DeleteRow()
On Error Resume Next
Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
On Error GoTo 0
End Sub

Edit: Alphafrog beat me to it. Back to sleep
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top