Do not delete row with cell in Column B = 0

[GBExcel via OfficeKB.com]

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

 

[Ryan H]

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

 

[Chip Pearson]

Try

Sub AAA()
Dim N As Long
Dim J As Long
Dim K As Long
N = Application.InputBox( _
prompt:="Number of rows to delete", Type:=1)
If N <= 0 Then
Exit Sub
End If
If N >= ActiveSheet.Rows.Count Then
Exit Sub
End If

' If you want to prevent the deletion of all rows
' if a header row exists between ActiveCell and
' ActiveCell+N, uncomment the code below and
' delete all codde that follows the code below.
'If Application.WorksheetFunction.CountIf( _
' ActiveCell.EntireRow.Cells(1, "B").Resize(N, 1), 0) = 0 Then
' ActiveCell.EntireRow.Resize(N).Delete
'End If
K = ActiveCell.Row

' To skip over any rows where col B is 0, use the following:
For J = K + N - 1 To K Step -1
If StrComp(Cells(J, "B").Text, "0", vbTextCompare) <> 0 Then
Rows(J).Delete
End If
Next J

End Sub

This depending on what code is comment out, the code will delete N
rows ONLY IF there are no footers in rows ActiveCell.Row to
ActiveCell.Row+N-1. If there is one or more footer rows in this range,
no rows will be delete. Uncomment the comment block and delete the
code that follows that block to get this behavior. As written, the
code will delete rows between ActiveCell.Row and ActiveCell.Row+N-1 if
that row doesn't not contain a 0. This may or may not delete N rows.
It deletes N - (# rows w/out 0 in B) rows.

Note that the code looks for a numeric 0 value in column B, not a
quoted text "0" in column B.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

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

 

[GBExcel via OfficeKB.com]

Hi Ryan,

Appreciate the reply.

The line

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

produces an 'Expression expected.' error message?

I'm afraid I don't know enough about VBA to solve this.

GBExcel
=========

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

Hi,

[quoted text clipped - 34 lines]

GBExcel

 

[Chip Pearson]

The problem is not with the code but rather with line wrapping in your
browser. The code

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

should be on one line in VBA. You can split it into shorter lines with

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

The <space><underscore> line continuation character sequence indicates
that what appears in the editor as two lines is to be treated as one
line during complilation.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Hi Ryan,

Appreciate the reply.

The line

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

produces an 'Expression expected.' error message?

I'm afraid I don't know enough about VBA to solve this.

GBExcel
=========

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

Hi,

[quoted text clipped - 34 lines]

GBExcel