Delete two rows between a named range

[shanecb]

Please can someone assist me in trying to delete two rows in between a named range (not including the named ranges)? I currently have:

Sub ClearDMM()
Rows(Range("DMMCI").Row + 1 & ":" & (Range("DMMCI2").Row - 1)).Delete
End Sub


The problem is if the macro / button is run twice then as you'll see above ("DMMCI2").Row + 1 is the row above ("DMMCI").Row - 1 and therefore gets deleted.

Is there a better way to only delete rows in between a named range if the upper and lower row are not next to eachother? Or a totally different way?

Many thanks for your help,
Shane

 

[JoeMo]

See if this does what you want:

Sub ClearDMM()
Select Case Range("DMMCI").Row - Range("DMMCI2").Row
    Case Is > 1
    Rows(Range("DMMCI2").Row + 1 & ":" & (Range("DMMCI").Row - 1)).Delete
    Case Is < -1
    Rows(Range("DMMCI").Row + 1 & ":" & (Range("DMMCI2").Row - 1)).Delete
End Select
End Sub

 

[shanecb]

Thanks so much Joe. This worked a treat!!

 

[JoeMo]

Thanks so much Joe. This worked a treat!!

You are welcome - thanks for the reply.