delete rows
 

Hi,

In Excel I have named ranges, for example rows 13:23 are a named rang
called "RijenFundering". When I select a cell in row 23 and use th
macro below to insert a row, then the empty row will be row 24. That i
outside the named range.
I also have a macro (see the second one below) to delete a row, bu
with this macro I cannot delete row 24, because it is outside the name
range. I tried something like Range("RijenFundering" + 1) but I get a
error then.
How should one of these macros be rewritten, so I am able to delete ro
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 Nothin
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 Nothin
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 Su

--
leonida
-----------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...fo&userid=3537
View this thread: http://www.excelforum.com/showthread.php?threadid=55489

delete rows
 

Hi leonidas,

What you need is a dyanamic range. I do it like this:

Hopefully you have a Dutch version of Excel (I cannot explain in it english
formulas)

Type in some cell (Blad1!A1) the first cellname of the range. For example:
Data!A1 (yes, type this in a cell)
Second step: type this in the cell under (Blad1!A2):
=tekst.samenvoegen("Data!A";aantalarg(Data!A:A)) The value of this cell must
be cellname of the last row in you range.
Third step: make a name range: Insert - name. Make it: yourname. In the
"bereik" you can type: =indirect(Blad1!$A$1):indirect(Blad1!$B$1).

Now you have a dynamic range wich you can use in your macro.

Gert-Jan


"leonidas" schreef
in bericht ...

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=554896