Macro for deleting rows
 

Hi,

In Excel I have named ranges, for example rows 13:23 are a named range
called "RijenFundering". When I select a cell in row 23 and use the
macro below to insert a row, then the empty row will be row 24. That is
outside the named range.
I also have a macro (see the second one below) to delete a row, but
with this macro I cannot delete row 24, because it is outside the named
range. I tried something like Range("RijenFundering" + 1) but I get an
error then.
How should one of these macros be rewritten, so I am able to delete row
24?
Thank in advance for helping me!

Sub InsertRows()

If Intersect(ActiveCell, Union(Range("RijenFundering"), _
Range("RijenBeganeGrondvloer"), Range("RijenMetselwerkenMinPeil"), _
Range("RijenBetonskeletBetonvloer"), Range("RijenKZSWandBetonvloer"),
_
Range("RijenPrefabBetonBuitenspouwblad"),
Range("RijenStaalconstructie"), _
Range("RijenStaalwerk"), Range("RijenMetselwerken"), _
Range("RijenGevelsluitendeElementen"), Range("RijenDakconstructie"), _
Range("RijenPrefabElementen"), Range("RijenDiversen"))) Is Nothing
Then
MsgBox "U kunt hier geen rij invoegen"
Else
Call InsertRows1
End If

End Sub
Private Sub InsertRows1()

Application.ScreenUpdating = False
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
Selection.Offset(1).EntireRow.SpecialCells(xlConst ants).ClearContents

End Sub
--------------------------------------------------------------------------
Sub DeleteRows()

If Intersect(ActiveCell, Union(Range("RijenFundering"), _
Range("RijenBeganeGrondvloer"), Range("RijenMetselwerkenMinPeil"), _
Range("RijenBetonskeletBetonvloer"), Range("RijenKZSWandBetonvloer"),
_
Range("RijenPrefabBetonBuitenspouwblad"),
Range("RijenStaalconstructie"), _
Range("RijenStaalwerk"), Range("RijenMetselwerken"), _
Range("RijenGevelsluitendeElementen"), Range("RijenDakconstructie"), _
Range("RijenPrefabElementen"), Range("RijenDiversen"))) Is Nothing
Then
MsgBox "U kunt deze rij niet verwijderen"
Else
Call DeleteRows1
End If

End Sub
Private Sub DeleteRows1()

Application.ScreenUpdating = False
ActiveCell.EntireRow.Delete

End Sub


--
leonidas
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=554945

Macro for deleting rows
 

have you tried
rg.Offset(1,0)

"leonidas" wrote:


Hi,

In Excel I have named ranges, for example rows 13:23 are a named range
called "RijenFundering". When I select a cell in row 23 and use the
macro below to insert a row, then the empty row will be row 24. That is
outside the named range.
I also have a macro (see the second one below) to delete a row, but
with this macro I cannot delete row 24, because it is outside the named
range. I tried something like Range("RijenFundering" + 1) but I get an
error then.
How should one of these macros be rewritten, so I am able to delete row
24?
Thank in advance for helping me!

Sub InsertRows()

If Intersect(ActiveCell, Union(Range("RijenFundering"), _
Range("RijenBeganeGrondvloer"), Range("RijenMetselwerkenMinPeil"), _
Range("RijenBetonskeletBetonvloer"), Range("RijenKZSWandBetonvloer"),
_
Range("RijenPrefabBetonBuitenspouwblad"),
Range("RijenStaalconstructie"), _
Range("RijenStaalwerk"), Range("RijenMetselwerken"), _
Range("RijenGevelsluitendeElementen"), Range("RijenDakconstructie"), _
Range("RijenPrefabElementen"), Range("RijenDiversen"))) Is Nothing
Then
MsgBox "U kunt hier geen rij invoegen"
Else
Call InsertRows1
End If

End Sub
Private Sub InsertRows1()

Application.ScreenUpdating = False
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
Selection.Offset(1).EntireRow.SpecialCells(xlConst ants).ClearContents

End Sub
--------------------------------------------------------------------------
Sub DeleteRows()

If Intersect(ActiveCell, Union(Range("RijenFundering"), _
Range("RijenBeganeGrondvloer"), Range("RijenMetselwerkenMinPeil"), _
Range("RijenBetonskeletBetonvloer"), Range("RijenKZSWandBetonvloer"),
_
Range("RijenPrefabBetonBuitenspouwblad"),
Range("RijenStaalconstructie"), _
Range("RijenStaalwerk"), Range("RijenMetselwerken"), _
Range("RijenGevelsluitendeElementen"), Range("RijenDakconstructie"), _
Range("RijenPrefabElementen"), Range("RijenDiversen"))) Is Nothing
Then
MsgBox "U kunt deze rij niet verwijderen"
Else
Call DeleteRows1
End If

End Sub
Private Sub DeleteRows1()

Application.ScreenUpdating = False
ActiveCell.EntireRow.Delete

End Sub


--
leonidas
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=554945