Try this. Hope this helps! If so, let me know, click "YES" below.

Dim RowNdx As Variant

RowNdx = Application.InputBox(Prompt:="Enter the number of the row you
want to delete")

If RowNdx = False Then
Exit Sub
ElseIf IsNumeric(RowNdx) And RowNdx 0 And _
RowNdx <= Rows.Count And Cells(RowNdx, "B") < "0" Then
Rows(RowNdx).Delete
Else
MsgBox "Stop! You may not delete the footer row.", vbCritical
End If

--
Cheers,
Ryan


"GBExcel via OfficeKB.com" wrote:

Hi,

I have a macro that allows a user to delete rows, which works great. However,
I want to prevent the user from deleting a footer row - (It contains the
legal notice and disclaimer.). The footer row can move up or down, depending
on how many rows have been added or removed above it. My thinking is to hide
a '0' in cell B of the footer row, by making the 0 the same color as the
background, and then letting the macro test for B(anyrow in a range)=0. If B
(anyrow in a range) = 0 and is the row the user wants to delete, show alert:
"Stop, you cannot delete the footer row!"

Here is my code so far:

' [I DON'T WANT THE FOOTER ROW DELETED THEREFORE IF B14=0 STOP THE DELETE.]
If Range("B14").Value "0" Then
'Run your code

' [THIS IS THE DELETE ROW CODE.]
Dim RowNdx As Long
RowNdx = Application.InputBox(prompt:="Enter the number of the row you want
to delete", Type:=1)
If RowNdx 0 And RowNdx <= Rows.Count Then
Rows(RowNdx).Delete
End If
'

' [B14 ALERT CONDITION NOT MET, THEREFORE SET ALERT]
Else: MsgBox "Stop! You may not delete the footer row."
Exit Sub
End If

The above works when the footer row moves up as other rows are deleted until
the footer row is in row 14. However, it does not work if the footer row is
in any other row and the user tries to delete it.

Help appreciated.

GBExcel

--
Message posted via http://www.officekb.com

.