Delete rows

[Guest]

I'm writing a macro to allow users to delete rows from the sheet. All they
have to do is select a cell in the table and press a shortcut. The entire row
where this cell is located will then be deleted. However, i want to prevent
them from deleting any rows below the last row in the table.
Because the table changes all the time (eg new rows get added), the row
number for the last row is a variable. to go around this, i gave the first
cell in the last row a name "Bottom". So how can i check that the cell
selected by the user (in the row they want to delete) is not situated below
"Bottom"?

The last row in my table has a different pattern and colour (to give a
visual indication that the table ends there).

Many thanks in advance

 

[Johz]

if activesheet.range("Bottom").row > selection.row then ' .row > selection.row because I assume you don't want to delete the "bottom" -row...
' // delete selected row
else
'// don't delete
end if

also check the CurrentRegion -property of the range-object, it is quite handy when dealing with tables. with currentregion you could get the range of the table without the named range "bottom" like this:

with activesheet.range("A1").currentregion '// assuming the table starts at cell a1
if .cells(.rows.count).row > selection.row then

' // delete selected row
else
'// don't delete
end if
end with

 

[Dave Mac]

I'm writing a macro to allow users to delete rows from the sheet. All they
have to do is select a cell in the table and press a shortcut. The entire row
where this cell is located will then be deleted. However, i want to prevent
them from deleting any rows below the last row in the table.
Because the table changes all the time (eg new rows get added), the row
number for the last row is a variable. to go around this, i gave the first
cell in the last row a name "Bottom". So how can i check that the cell
selected by the user (in the row they want to delete) is not situated below
"Bottom"?

The last row in my table has a different pattern and colour (to give a
visual indication that the table ends there).

Many thanks in advance

try something like:-

if activecell.row >= range("namedcell").row then
msgbox "Doh!"
exit sub
end if