I want to write a code that allows users of my spreadsheet to select multiple
rows and delete them. However, i need to check first that the selected rows
are located within a certain range, that is between row 12 and row 50. If the
selection is outside that range, then the user will get a message saying they
can't delete this selection.
How do you say the following in VBA?

If the current selection is located between row 12 and row 50 then
delete selection.
Else
msgbox "sorry, you can't delete."

Many thanks
Tendresse

Hope this helps.................

Option Explicit

Sub DeleteUserRowSelection()
Dim MinRow As Long, MaxRow As Long, UserStartRow As Long, UserEndRow As Long
Dim Msg As String, UserRange As Range, DefaultRange As String, Prompt As
String

MinRow = 12 'Allow delete from this row
MaxRow = 50 'Allow delete till this row
Prompt = "Please select a range to delete (row " & MinRow & " to " &
MaxRow & ")"
DefaultRange = Selection.Address

On Error GoTo Canceled
' Get user input (Let user select range)
Set UserRange = Application.InputBox _
(Prompt:=Prompt, _
Title:="DELETE ROWS", _
Default:=DefaultRange, _
Type:=8)
With UserRange
.Parent.Parent.Activate
.Parent.Select
.EntireRow.Select
End With

With Selection
UserStartRow = .Row
UserEndRow = .Rows.Count
If UserStartRow < MinRow Or UserEndRow MaxRow Then
Msg = "Sorry you can't delete this selection. " & vbCrLf & vbCrLf
Msg = Msg & "Allowable range for deletion is only from row " &
MinRow & " to " & MaxRow & ". "
MsgBox Msg, vbCritical
End
End If
.Delete
End With
Exit Sub
Canceled:
Msg = "Please try again. "
MsgBox Msg, vbInformation
End Sub





--
Edmund
(Using Excel XP)


"Tendresse" wrote:

I want to write a code that allows users of my spreadsheet to select multiple
rows and delete them. However, i need to check first that the selected rows
are located within a certain range, that is between row 12 and row 50. If the
selection is outside that range, then the user will get a message saying they
can't delete this selection.
How do you say the following in VBA?

If the current selection is located between row 12 and row 50 then
delete selection.
Else
msgbox "sorry, you can't delete."

Many thanks
Tendresse

Hi Edmund, thank you very much for your reply. That was really helpful. You
did put me in the right direction. Much appreciated.
I did some few changes to your code to better suit my spreadsheet. It works
perfectly, but i'm still stuck in one little step.

First, i'll show you the code after the changes i made:

In your code, you are asking the user to input the rows they want to delete.
I skipped this bit because i'm assuming the user has already selected the
rows they want to delete by clicking (and holding) the row(s) number(s) on
the left of the screen. So my code starts after the selection has already
been manually made.


Sub DeleteRow()

' I called the first cell in the last row in the table "Bottom"
' because as the table grows, the last row will be a variable

Dim rowNum As Integer
rowNum = Range("Bottom").Row

Dim UserStartRow As Integer, UserEndRow As Integer

With Selection
UserStartRow = .Row
UserEndRow = .Rows.Count + UserStartRow - 1

If UserStartRow < 12 Or UserEndRow rowNum Then

MsgBox "Sorry! You cannot delete this selection.", , "Stop"
Exit Sub

Else

.Delete
MsgBox "Row(s) deleted.", , "Done"

End If

End With

End Sub

Now, i only need to add one more line at the beginning of this code to check
that the selection is actually an entire row (or multiple rows), not an
individual cell(s).

How do you say in VBA:

if selection is not an entire row (or rows) then prompt the user and exit sub

Thank you again for your help.

Tendresse


"Edmund" wrote:

Hope this helps.................

Option Explicit

Sub DeleteUserRowSelection()
Dim MinRow As Long, MaxRow As Long, UserStartRow As Long, UserEndRow As Long
Dim Msg As String, UserRange As Range, DefaultRange As String, Prompt As
String

MinRow = 12 'Allow delete from this row
MaxRow = 50 'Allow delete till this row
Prompt = "Please select a range to delete (row " & MinRow & " to " &
MaxRow & ")"
DefaultRange = Selection.Address

On Error GoTo Canceled
' Get user input (Let user select range)
Set UserRange = Application.InputBox _
(Prompt:=Prompt, _
Title:="DELETE ROWS", _
Default:=DefaultRange, _
Type:=8)
With UserRange
.Parent.Parent.Activate
.Parent.Select
.EntireRow.Select
End With

With Selection
UserStartRow = .Row
UserEndRow = .Rows.Count
If UserStartRow < MinRow Or UserEndRow MaxRow Then
Msg = "Sorry you can't delete this selection. " & vbCrLf & vbCrLf
Msg = Msg & "Allowable range for deletion is only from row " &
MinRow & " to " & MaxRow & ". "
MsgBox Msg, vbCritical
End
End If
.Delete
End With
Exit Sub
Canceled:
Msg = "Please try again. "
MsgBox Msg, vbInformation
End Sub





--
Edmund
(Using Excel XP)


"Tendresse" wrote:

I want to write a code that allows users of my spreadsheet to select multiple
rows and delete them. However, i need to check first that the selected rows
are located within a certain range, that is between row 12 and row 50. If the
selection is outside that range, then the user will get a message saying they
can't delete this selection.
How do you say the following in VBA?

If the current selection is located between row 12 and row 50 then
delete selection.
Else
msgbox "sorry, you can't delete."

Many thanks
Tendresse

Sorry Tendresse. I actuallyl forgot to mention the below in my reply.

When the dialogbox pops up, the user can can specify the "range to delete"
by using the mouse. Just select the range (say D22:G44) & VBA will deletect
the entire row of the selected range. Just navigate with your mouse to your
worksheet, select the some cells within row 12 to 50, and press OK. Try &
see.

Hope that helps.
--
Edmund
(Using Excel XP)

Dim rowNum As Integer
rowNum = Range("Bottom").Row

Dim UserStartRow As Integer, UserEndRow As Integer

With Selection
UserStartRow = .Row
UserEndRow = .Rows.Count + UserStartRow - 1

If (UserStartRow < 12 Or UserEndRow rowNum) and _
.Columns.Count < Columns.count Then

MsgBox "Sorry! You cannot delete this selection.", , "Stop"
Exit Sub

Else

.Delete
MsgBox "Row(s) deleted.", , "Done"

End If

End With

the method you have won't work if the user selects non-contiguous
cells/rows. Don't know if that is a concern.

--
Regards,
Tom Ogilvy



"Tendresse" wrote:

Hi Edmund, thank you very much for your reply. That was really helpful. You
did put me in the right direction. Much appreciated.
I did some few changes to your code to better suit my spreadsheet. It works
perfectly, but i'm still stuck in one little step.

First, i'll show you the code after the changes i made:

In your code, you are asking the user to input the rows they want to delete.
I skipped this bit because i'm assuming the user has already selected the
rows they want to delete by clicking (and holding) the row(s) number(s) on
the left of the screen. So my code starts after the selection has already
been manually made.


Sub DeleteRow()

' I called the first cell in the last row in the table "Bottom"
' because as the table grows, the last row will be a variable

Dim rowNum As Integer
rowNum = Range("Bottom").Row

Dim UserStartRow As Integer, UserEndRow As Integer

With Selection
UserStartRow = .Row
UserEndRow = .Rows.Count + UserStartRow - 1

If UserStartRow < 12 Or UserEndRow rowNum Then

MsgBox "Sorry! You cannot delete this selection.", , "Stop"
Exit Sub

Else

.Delete
MsgBox "Row(s) deleted.", , "Done"

End If

End With

End Sub

Now, i only need to add one more line at the beginning of this code to check
that the selection is actually an entire row (or multiple rows), not an
individual cell(s).

How do you say in VBA:

if selection is not an entire row (or rows) then prompt the user and exit sub

Thank you again for your help.

Tendresse


"Edmund" wrote:

Hope this helps.................

Option Explicit

Sub DeleteUserRowSelection()
Dim MinRow As Long, MaxRow As Long, UserStartRow As Long, UserEndRow As Long
Dim Msg As String, UserRange As Range, DefaultRange As String, Prompt As
String

MinRow = 12 'Allow delete from this row
MaxRow = 50 'Allow delete till this row
Prompt = "Please select a range to delete (row " & MinRow & " to " &
MaxRow & ")"
DefaultRange = Selection.Address

On Error GoTo Canceled
' Get user input (Let user select range)
Set UserRange = Application.InputBox _
(Prompt:=Prompt, _
Title:="DELETE ROWS", _
Default:=DefaultRange, _
Type:=8)
With UserRange
.Parent.Parent.Activate
.Parent.Select
.EntireRow.Select
End With

With Selection
UserStartRow = .Row
UserEndRow = .Rows.Count
If UserStartRow < MinRow Or UserEndRow MaxRow Then
Msg = "Sorry you can't delete this selection. " & vbCrLf & vbCrLf
Msg = Msg & "Allowable range for deletion is only from row " &
MinRow & " to " & MaxRow & ". "
MsgBox Msg, vbCritical
End
End If
.Delete
End With
Exit Sub
Canceled:
Msg = "Please try again. "
MsgBox Msg, vbInformation
End Sub





--
Edmund
(Using Excel XP)


"Tendresse" wrote:

I want to write a code that allows users of my spreadsheet to select multiple
rows and delete them. However, i need to check first that the selected rows
are located within a certain range, that is between row 12 and row 50. If the
selection is outside that range, then the user will get a message saying they
can't delete this selection.
How do you say the following in VBA?

If the current selection is located between row 12 and row 50 then
delete selection.
Else
msgbox "sorry, you can't delete."

Many thanks
Tendresse

Hey Guys, thank you very much for your help.
I took a bit of each of your replies and ended up with the perfect code.
Tendresse :)

"Tom Ogilvy" wrote:

Dim rowNum As Integer
rowNum = Range("Bottom").Row

Dim UserStartRow As Integer, UserEndRow As Integer

With Selection
UserStartRow = .Row
UserEndRow = .Rows.Count + UserStartRow - 1

If (UserStartRow < 12 Or UserEndRow rowNum) and _
.Columns.Count < Columns.count Then

MsgBox "Sorry! You cannot delete this selection.", , "Stop"
Exit Sub

Else

.Delete
MsgBox "Row(s) deleted.", , "Done"

End If

End With

the method you have won't work if the user selects non-contiguous
cells/rows. Don't know if that is a concern.

--
Regards,
Tom Ogilvy



"Tendresse" wrote:

Hi Edmund, thank you very much for your reply. That was really helpful. You
did put me in the right direction. Much appreciated.
I did some few changes to your code to better suit my spreadsheet. It works
perfectly, but i'm still stuck in one little step.

First, i'll show you the code after the changes i made:

In your code, you are asking the user to input the rows they want to delete.
I skipped this bit because i'm assuming the user has already selected the
rows they want to delete by clicking (and holding) the row(s) number(s) on
the left of the screen. So my code starts after the selection has already
been manually made.


Sub DeleteRow()

' I called the first cell in the last row in the table "Bottom"
' because as the table grows, the last row will be a variable

Dim rowNum As Integer
rowNum = Range("Bottom").Row

Dim UserStartRow As Integer, UserEndRow As Integer

With Selection
UserStartRow = .Row
UserEndRow = .Rows.Count + UserStartRow - 1

If UserStartRow < 12 Or UserEndRow rowNum Then

MsgBox "Sorry! You cannot delete this selection.", , "Stop"
Exit Sub

Else

.Delete
MsgBox "Row(s) deleted.", , "Done"

End If

End With

End Sub

Now, i only need to add one more line at the beginning of this code to check
that the selection is actually an entire row (or multiple rows), not an
individual cell(s).

How do you say in VBA:

if selection is not an entire row (or rows) then prompt the user and exit sub

Thank you again for your help.

Tendresse


"Edmund" wrote:

Hope this helps.................

Option Explicit

Sub DeleteUserRowSelection()
Dim MinRow As Long, MaxRow As Long, UserStartRow As Long, UserEndRow As Long
Dim Msg As String, UserRange As Range, DefaultRange As String, Prompt As
String

MinRow = 12 'Allow delete from this row
MaxRow = 50 'Allow delete till this row
Prompt = "Please select a range to delete (row " & MinRow & " to " &
MaxRow & ")"
DefaultRange = Selection.Address

On Error GoTo Canceled
' Get user input (Let user select range)
Set UserRange = Application.InputBox _
(Prompt:=Prompt, _
Title:="DELETE ROWS", _
Default:=DefaultRange, _
Type:=8)
With UserRange
.Parent.Parent.Activate
.Parent.Select
.EntireRow.Select
End With

With Selection
UserStartRow = .Row
UserEndRow = .Rows.Count
If UserStartRow < MinRow Or UserEndRow MaxRow Then
Msg = "Sorry you can't delete this selection. " & vbCrLf & vbCrLf
Msg = Msg & "Allowable range for deletion is only from row " &
MinRow & " to " & MaxRow & ". "
MsgBox Msg, vbCritical
End
End If
.Delete
End With
Exit Sub
Canceled:
Msg = "Please try again. "
MsgBox Msg, vbInformation
End Sub





--
Edmund
(Using Excel XP)


"Tendresse" wrote:

I want to write a code that allows users of my spreadsheet to select multiple
rows and delete them. However, i need to check first that the selected rows
are located within a certain range, that is between row 12 and row 50. If the
selection is outside that range, then the user will get a message saying they
can't delete this selection.
How do you say the following in VBA?

If the current selection is located between row 12 and row 50 then
delete selection.
Else
msgbox "sorry, you can't delete."

Many thanks
Tendresse