PDA

View Full Version : Deleting all rows of named merged range



faarikaal
03-06-2009, 04:50 AM
Im trying to delete all rows within a merged range. I have got it working but there has to be a better/alternative way of doing this

Set rngTemp = mySheet.Range([NamedRange]
intStart = rngTemp.Row
intStop = rngTemp.End(xlDown).Row -2
mySheet.Rows(intStart & ":" & intStop).EntireRow.Delete

FYI!
For some reason the .End(xlDown) always returns 2 rows bellow actual last row of merged range

Any suggestions?

Bob Phillips
03-06-2009, 04:59 AM
Set rngTemp = mySheet.Range("NamedRange")
rngTemp.Entirerow.Delete

faarikaal
03-06-2009, 05:01 AM
Tried this one but it actually only deletes the top row of the merged range.

Bob Phillips
03-06-2009, 05:09 AM
Not for me it doesn't. What does NamedRange refer to?

faarikaal
03-06-2009, 05:28 AM
It refers to a named range which is actually set to the top left cell of the merged range when i think about it...
I assumed that if i refer to the named range i get the entire contents of the merged area that cell is in, which might not be the case.

I tried with .Range([NamedRange]).MergeArea(1,1).EntireRow.Delete but it didnt work.

Any suggestions?

BTW: Thank you for a quick reply!

Bob Phillips
03-06-2009, 05:44 AM
You should have mentioned that earlier, it is very pertinent



If rngTemp.MergeCells Then

rngTemp.MergeArea.EntireRow.Delete
Else

rngTemp.EntireRow.Delete
End If