Ensure Selection Row Deletion Doesn't Extend Past Certain Row

outpatient · May 26th 2008

I have a delete button on a protected sheet to allow a user to delete selected rows (unprotects, deletes, protects again...).

I need to add a check to make sure they do not delete any row greater than 152. How can I check if a row greater than 152 is in their range of selected rows?

Thanks

AAE · May 26th 2008

Re: Check If A Certain Row Number Is In A Selection

Here's a start:

Sub DeleteRows()

Code

If ActiveCell.Row > 152 Then
    MsgBox "Row deletion not permitted"
    
Else
    ActiveCell.EntireRow.Delete
    
End If


End Sub

VBA Noob · May 26th 2008

Re: Check If A Certain Row Number Is In A Selection

If the user has more than one row selected then try

Code

If Selection.Cells(Selection.Cells.Count).Row >= 152 Then
    MsgBox "Cannot delete beyond row 152!!"
Else
    Selection.EntireRow.Delete
End If

VBA Noob

Bill Rockenbach · May 26th 2008

Re: Check If A Certain Row Number Is In A Selection

If a user deletes a row below 153, row 153 becomes row 152. Is the user now allowed to delete row 152

Edited

outpatient · May 26th 2008

Re: Check If A Certain Row Number Is In A Selection

Yeah, the way the sheet was set up (not by me), there is a totals row at 153 and some other info after that which the user should not be able to delete. So I ran into the problem that bill mentioned, where 153 became 152, which doesn't work because I need that row to always be at 153.

I ended up clearing the contents (except the fomulas), cutting the rows and moving the to the bottom of the range. It's not too elegant, but it works:

Code

Private Sub cmdDelete_Click()


Dim ProgramsWorkSheet As Worksheet
Set ProgramsWorkSheet = Sheets("Programs")
Dim NumRows As Integer
Dim SelStr As String


On Error GoTo Catch
    'Make sure row being "deleted" (cleared) is between 3 and 152
    If Selection.Rows.Row > 2 And Selection.Rows.Row < 153 Then
        
        If Selection.Rows.Count > 0 Then
            'unprotect to allow the move
            ProgramsWorkSheet.Unprotect (Password)
            'clear the rows (can't delete - need row 153 to stay put)
            Selection.Rows.SpecialCells(xlCellTypeConstants).ClearContents
            NumRows = Selection.Rows.Count
            'cut the cleared rows and put them at the bottom.  Hide Them with the others  
            Selection.Rows.Cut
            Range("A153").Select
            Selection.EntireRow.Insert
            SelStr = "A" & 153 - NumRows & ":A152"
            Range(SelStr).Select
            Selection.EntireRow.Hidden = True
                                    
            'protect
            Call ProgramsWorkSheet.Protect(Password:=Password, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
                                           AllowFormattingRows:=True, AllowInsertingColumns:=False, AllowInsertingRows:=False, _
                                           AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True, _
                                           AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=False)
        Else
            MsgBox "Select rows to delete first"
        End If
    Else
        MsgBox "Cannot delete this row"
    End If
Catch:


If Err.Description <> "" Then
    MsgBox "Error: " & Err.Description
End If
    
End Sub

Display More

Thanks for the replies

AAE · May 26th 2008

Re: Check If A Certain Row Number Is In A Selection

Can you put your totals row at the top? This would avoid the issue if you have the option to do it.

**mikerickson** · May 26th 2008

Re: Check If A Certain Row Number Is In A Selection

Another approach would be to make the "may not be deleted" range a named range. Then

Code

If Not(Application.Intersect (Range("DoNotDeleteMe"),Selection) is Nothing) Then
    Selection.Delete
End If

would protect that range, while Excel keeps track of where insertions and deletions have moved DoNotDeleteMe.

Dave Hawley · May 27th 2008

If you wish to continue using this free service.

Please be considerate of others who use the forum for searching. Your current Thread Title (which I will change) is non-reflective of your immediate problem.

In future, please take 1 minute of your time to read the text on the New Thread page.

[fa]*[/fa]

REMEMBER: Your thread title should NEVER be what you THINK is your answer. 9 times out 10 it will wrong and prevent someone from finding a solution to a simliar issue.

Ensure Selection Row Deletion Doesn't Extend Past Certain Row

Participate now!

Share