Rows.Delete not deleting first row in range

Wouldn't have thought so. Nothing to do with the looped range. You could use Or instead of ElseIf.

More likely the data isn’t what you think it is. Can't help there without seeing the file.

Hi,

Maybe because the 9th iteration of the loop isn't processed since r is incremented at the last but one NEXT instruction.

However looping just isn't the best way of doing this sort of thing.

Whenever you find yourself creating looping code stop and think is this really necessary? Most of the time it isn't. Loops are inherently slow when they have to jump back and forth between VBA and the Excel application - there's a big time overhead at each pass through the loop.

The fastest, most efficient and elegant way for this sort of stuff is to use an Autofilter to filter the rows you're interested in.

Application.DisplayAlerts = False
    With Range("A8:H8")
        .CurrentRegion.AutoFilter field:=8, Criteria1:=True
        .CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows.Delete
        .AutoFilter
        End With

This uses just one Delete instruction rather than many if you're looping through many cells.

Originally Posted by hrlngrv

Quibble: AutoFilter requires having a column on which to apply it. You should have been explicit about that.

The code I offered did necessarily show the field on which to filter. It would have debugged otherwise.
When you say being 'explicit' what did you have in mind?

The essential point was to make the point that loops should be the last resort for stuff like this

Make use of formula in helper column and delete all matching at once with autofilter...

While there have been several alternative ways offered of achieving the intended goal, that is really not the point. Without seeing the file, it is impossible to guess at the reason that row 9 is not deleted IF, as stated, it meets the criteria.

There is nothing wrong with your code. It is, therefore the data. I suspect that, instead of "True ", you have "TRUE" without the trailing space. Whatever, that cannot be proven without the workbook.

In the absence of that workbook and, as a sign of frustration, I have created one and tested the code, and some variations. It is attached.

The code variations:

Option Explicit

Sub DeleteFiltered_TMS()
' variation on the original
' delete rows from bottom up, one by one

Dim r As Long
Dim LastRow As Long
Const Row9 As Long = 9

Application.ScreenUpdating = False

LastRow = Cells(Rows.Count, "H").End(xlUp).Row
For r = LastRow To Row9 Step -1
    If Cells(r, "H") > -5 And Cells(r, "H") < 0 And Cells(r, "I") = "True " _
    Or Cells(r, "G") > 0 And Cells(r, "G") < 5 And Cells(r, "I") = "True " _
    Then
        Rows(r).Delete
    End If
Next 'r

Application.ScreenUpdating = True

End Sub

Sub DeleteFiltered_TMS_v2()
' variation on the original
' delete rows from bottom up, en bloc
' that is, a single delete

Dim r As Long
Dim LastRow As Long
Const Row9 As Long = 9
Dim rng As Range

Application.ScreenUpdating = False

LastRow = Cells(Rows.Count, "H").End(xlUp).Row
For r = LastRow To Row9 Step -1
    If Cells(r, "H") > -5 And Cells(r, "H") < 0 And Cells(r, "I") = "True " _
    Or Cells(r, "G") > 0 And Cells(r, "G") < 5 And Cells(r, "I") = "True " _
    Then
        If rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r))
    End If
Next 'r

On Error Resume Next
rng.EntireRow.Delete
On Error GoTo 0

Application.ScreenUpdating = True

End Sub

Updated version incorporating Richard Buttrey's solution, reflecting hrlngrv's comments.

Sub DeleteFiltered_TMS_v3()
' variation on the original
' select rows using AutoFilter
' that is, a single delete
' courtesy of Richard Buttrey
' with formula courtesy of hrlngrv (in column J)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

With Range("A8:J8")
    .CurrentRegion.AutoFilter _
        field:=10, _
        Criteria1:=True
    .CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows.Delete
    .AutoFilter
End With
Application.ScreenUpdating = True

End Sub

You have a leading space on the text in row 9, column I (cell I9)

Not that it matters, but your code is password protected so we can't see your code.

Your code tests for "Till Difference CASH ", that is, with a trailing space. If you remove trailing spaces, your code will not work ... unless you also remove it from your test in the code.

But as was mentioned earlier this coding causes it to run slow so is there another way to do it.

You have been given at least three options ... and I have summarised and tested them. At least two of them use a single delete. I have taken the suggestions from Richard Buttrey and hrlngrv and refined and tested them. They both use only a single delete, although Richard's AutoFilter suggestion will require a Helper column as described by hrlngrv.

The second example workbook has ALL the code variations. Try them and see which works best for you.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

This should delete all columns E:K and shiftup when...

So not delete all rows...

But as was mentioned earlier this coding causes it to run slow so is there another way to do it.

Making use of sample file supplied in post 14 and suggested helper column with formula

Sub sintekJ3v16()
Dim Data, Arr
With Range("E9:L" & Cells(Rows.Count, 6).End(xlUp).Row)
    .Columns(8).Formula = "=AND(I9=""Till Difference CASH "",OR(AND(-5<H9,H9<0),AND(0<G9,G9<5)))"
    Data = Filter(Evaluate("Transpose(If(" & .Columns(8).Address & "=FALSE,row(1:" & .Rows.Count & ")))"), False, 0)
    If UBound(Data) > -1 Then Arr = Application.Index(.Value, Application.Transpose(Data), Array(1, 2, 3, 4, 5, 6, 7))
    .ClearContents
End With
Range("E9").Resize(UBound(Arr, 1), 7) = Arr
End Sub