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.
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
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.
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