Delete row i and next row in VBA

Question

I'm looking to run a for loop which detects a zero value and deletes the specific rows. The trouble I am having is that the target column is merged cells (2no. rows merged).

The below code works, but only deletes the first of the two rows, leaving the other behind. How do I expand i to include the next row down?

Thanks in advance.

    Dim i As Long, Lr As Long
    Lr = Range("A" & Rows.Count).End(xlUp).Row

    For i = Lr To 1 Step -1
    If Range("O" & i).Value = "" Then GoTo NextI
    If Range("O" & i).Value = 0 And IsNumeric(Range("O" & i).Value) Then Rows(i).Delete

    NextI:
    Next i

Toddleson · Accepted Answer · 2021-12-24 16:55:52Z

2

Testing for Merge-ness

If you have a mix of merged cells and unmerged cells you can test for merged-ness with the Range.MergeCells property.

Referencing Merged Ranges

The Range.MergeArea property will give you the full merged range from a single cell. So using Range.MergeArea.EntireRow.Delete will delete the all rows of your merged cell.

Values from Merged Ranges:

A common issue I run into is trying to test the value of a cell in a merged area and getting Empty or Blank values. This is because only the Top Left cell of a merged area actually holds a value, and the others are blank. For single cells .MergeArea returns itself even when not merged, so With a mix of merged and unmerged cells you can do Range.MergeArea.Cells(1).Value instead of Range.Value and not worry about testing for merge-ness. This will ensure you get the display value of a merged area instead of a blank.

Here is your code with these ideas added in:

    Dim i As Long, Lr As Long
    Lr = Range("A" & Rows.Count).End(xlUp).Row

    For i = Lr To 1 Step -1
    Dim val As Variant
    With Range("O" & i)
        val = .MergeArea.Cells(1).Value
        If val = "" Then GoTo NextI
        If val = 0 And IsNumeric(val) Then .MergeArea.EntireRow.Delete
    End With
NextI:
    Next i

answered Dec 24, 2021 at 16:55

Toddleson

4,4852 gold badges9 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

VBasic2008 Over a year ago

Nice. Using Goto outside of error-handling is considered bad practice and is unnecessary in this case. You could try e.g. If Not IsEmpty(Val) Then : If IsNumeric(Val) Then : If Val = 0 Then, each in a separate row (I know you know the latter, but someone might not). This also prevents the error that would occur if the cell contained an error value.

Toddleson Over a year ago

@VBasic2008 I agree, I left it in the my answers code because it was included in the original posted code, but if I were to rewrite this in my own style, I would much prefer using the If Statement to skip to the end of the loop than to use GoTo.

Collectives™ on Stack Overflow

Delete row i and next row in VBA

1 Answer 1

Testing for Merge-ness

Referencing Merged Ranges

Values from Merged Ranges:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Testing for Merge-ness

Referencing Merged Ranges

Values from Merged Ranges:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related