Thread: Verify Active Cell is within a Range that changes as cell is delet

I'd like to verify a cell is within a range of rows prior to allowing the
macro to delete the row. It's easy enough I know if the range of rows is
constant; but if you're deleting the rows within the range - then the range
is shrinking and using constants (as in my example below) probably won't
work. Any ideas?

If ActiveCell.Row >6 AND ActiveCell.Row <30 Then
ActiveCell.Row.Delete Else
MsgBox "Nope - can't delete the row."
End If

How are the row number limits determined? If you have a defined
name for the range, you can write

If Not Application.Intersect(ActiveCell, Range("Your")) Is
Nothing Then
ActiveCell.EntireRow.Delete
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Shannon" <Shannon@discussions.microsoft.com> wrote in message
news:74606B51-717B-431F-90FC-B2BCF81B0765@microsoft.com...
> I'd like to verify a cell is within a range of rows prior to
> allowing the
> macro to delete the row. It's easy enough I know if the range
> of rows is
> constant; but if you're deleting the rows within the range -
> then the range
> is shrinking and using constants (as in my example below)
> probably won't
> work. Any ideas?
>
> If ActiveCell.Row >6 AND ActiveCell.Row <30 Then
> ActiveCell.Row.Delete Else
> MsgBox "Nope - can't delete the row."
> End If
>
>

You probably want to use a named range as it can gor and shrink as the sheet
is changed. Then you can just check to see if the active cell is within the
named range something like this...

dim rng as range

on error resume next
set rng = intersect(activecell, range("MyRange"))
on error goto 0
if rng is nothing then
activecell.entirerow.delete
else
msgbox "Sorry. No can do"
endif

--
HTH...

Jim Thomlinson


"Shannon" wrote:

> I'd like to verify a cell is within a range of rows prior to allowing the
> macro to delete the row. It's easy enough I know if the range of rows is
> constant; but if you're deleting the rows within the range - then the range
> is shrinking and using constants (as in my example below) probably won't
> work. Any ideas?
>
> If ActiveCell.Row >6 AND ActiveCell.Row <30 Then
> ActiveCell.Row.Delete Else
> MsgBox "Nope - can't delete the row."
> End If
>
>