delete rows

[leonidas]

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(xlConstants).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

 

[Gert-Jan]

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