Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not delete row with cell in Column B = 0
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not delete row with cell in Column B = 0
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not delete row with cell in Column B = 0
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] On Wed, 27 Jan 2010 18:06:16 GMT, "GBExcel via OfficeKB.com" <u55438@uwe 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not delete row with cell in Column B = 0
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 ========= Ryan H wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201001/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not delete row with cell in Column B = 0
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] On Wed, 27 Jan 2010 19:39:00 GMT, "GBExcel via OfficeKB.com" <u55438@uwe wrote: 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 ========= Ryan H wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete numbers in each cell in same column | Excel Worksheet Functions | |||
Delete row with empty cell in column | Excel Discussion (Misc queries) | |||
Delete Rows if any cell in Column H is blank but do not Delete Fir | Excel Programming | |||
delete a cell with a null value in column n | Excel Programming | |||
How to delete the first word in every cell in a column | Excel Worksheet Functions |