Bad Code needs your brain to delete row in range

[Marthaljordan]

I'm a very frustrated (but persistent) newbie using VBA. My worksheet must be protected due to complext formulas IN the table, and above it as well. Therefore, to allow users to delete rows in the table, I must use a macro button. Using an InputBox, the rows above 24 can't be deleted... the rows below can. But the code below doesn't work... it pops up and simply cancels with all attempts.


'Sub DeleteARow()

'

'

'Dim rRange As Range

'

' On Error Resume Next

' Application.DisplayAlerts = False

' Set rRange = Application.InputBox(Prompt:="Enter row number to delete (must be greater than 23). Once deleted, it can't be undone.", _

' Title:="ENTER ROW NUMBER", Default:="24")

' On Error GoTo 0

' Application.DisplayAlerts = True

'

' If rRange Is Nothing Then

' Exit Sub

'

' Else

' Select Case rRange

'

' Case Is <= 23

' Exit Sub

'

' Case Is >= 24

'

'Turn off password protection...

' ActiveSheet.Unprotect Password:="Password"

'Then, do This stuff...

' rRange.Rows(1).Delete

'Turn off password protection back on...

' ActiveSheet.Protect Password:="Password", _

DrawingObjects:=True, Contents:=True, Scenarios:=True _

, AllowDeletingRows:=True, AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True

' Application.ScreenUpdating = True

'

' End Select

' End If

'

'

'End Sub

 

[vijaySharma]

Not sure if this post is still needing an answer...


but if someone else may benefit... the below code is the answer


Sub DeleteARow()


Dim rRange As Long


On Error Resume Next

Application.DisplayAlerts = False

rRange = InputBox(Prompt:="Enter row number to delete (must be greater than 23). Once deleted, it can't be undone.", _

Title:="ENTER ROW NUMBER", Default:="24")

On Error GoTo 0

Application.DisplayAlerts = True


If IsEmpty(rRange) Then

Exit Sub


Else

Select Case rRange

Case Is <= 23

Exit Sub

Case Is >= 24

'Turn off password protection...

ActiveSheet.Unprotect Password:="Password"

'Then, do This stuff...

Rows(rRange).Select

ActiveCell.EntireRow.Delete

'rRange.Rows(1).Delete

'Turn off password protection back on...

ActiveSheet.Protect Password:="Password", _

DrawingObjects:=True, Contents:=True, Scenarios:=True _

, AllowDeletingRows:=True, AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True

Application.ScreenUpdating = True

End Select

End If


End Sub


~Vijay

 

[vijaySharma]

Sub DeleteARow()


Dim rRange As Long


On Error Resume Next

Application.DisplayAlerts = False

rRange = InputBox(Prompt:="Enter row number to delete (must be greater than 23). Once deleted, it can't be undone.", _

Title:="ENTER ROW NUMBER", Default:="24")

On Error GoTo 0

Application.DisplayAlerts = True


If IsEmpty(rRange) Then

Exit Sub


Else

Select Case rRange

Case Is <= 23

Exit Sub

Case Is >= 24

'Turn off password protection...

ActiveSheet.Unprotect Password:="Password"

'Then, do This stuff...

Rows(rRange).Select

ActiveCell.EntireRow.Delete

'rRange.Rows(1).Delete

'Turn off password protection back on...

ActiveSheet.Protect Password:="Password", _

DrawingObjects:=True, Contents:=True, Scenarios:=True _

, AllowDeletingRows:=True, AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True

Application.ScreenUpdating = True

End Select

End If


End Sub