InputBox method with range
 

I am trying to use an InputBox method to ask the user for a range of rows.
It works most of the time, but sometimes returns nothing, even though a range
was chosen. It is also inconsistent - sometimes a chosen range is
recognized, and sometimes the same range is not recognized. What gives?
(Excel 2000 on Windows XP)

Sub Delete_Rows()

Dim Target As Range
Dim SpecialRows() As Integer
Dim i As Integer
Dim BadRowsList As String
Dim CancelDel As Boolean
Dim EndCleared As Range

CancelDel = False

On Error Resume Next
Title = " "
Prompt = "Select row (or range of rows) to be deleted."

Set Target = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8)
If Target Is Nothing Then
Exit Sub
Else
Application.ScreenUpdating = False
ReDim SpecialRows(Target.Rows.Count - 1) As Integer
i = 0
Worksheets(1).Unprotect ("support")
For Each rw In Target.Rows
If rw.Row < 7 Then
SpecialRows(i) = rw.Row
i = i
CancelDel = True
End If
Next
If CancelDel = True Then
MsgBox "Rows 1 - 6 cannot be deleted."
Else
Target.EntireRow.Delete
End If
Worksheets(1).Protect ("support")
End If
Application.ScreenUpdating = True

End Sub

InputBox method with range
 

Emily
when you say"It is also inconsistent - sometimes a chosen range is
recognized, and sometimes the same range is not recognized."
Do mean that 1) the input does not come up with the activecell.address
or 2)you type the range in the input box and it just does not delete?

Try disabling the on error resume next and see where you are getting
the error? this might gives some idea of what is going on.

Emily Edgington wrote:
I am trying to use an InputBox method to ask the user for a range of rows.
It works most of the time, but sometimes returns nothing, even though a range
was chosen. It is also inconsistent - sometimes a chosen range is
recognized, and sometimes the same range is not recognized. What gives?
(Excel 2000 on Windows XP)

Sub Delete_Rows()

Dim Target As Range
Dim SpecialRows() As Integer
Dim i As Integer
Dim BadRowsList As String
Dim CancelDel As Boolean
Dim EndCleared As Range

CancelDel = False

On Error Resume Next
Title = " "
Prompt = "Select row (or range of rows) to be deleted."

Set Target = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8)
If Target Is Nothing Then
Exit Sub
Else
Application.ScreenUpdating = False
ReDim SpecialRows(Target.Rows.Count - 1) As Integer
i = 0
Worksheets(1).Unprotect ("support")
For Each rw In Target.Rows
If rw.Row < 7 Then
SpecialRows(i) = rw.Row
i = i
CancelDel = True
End If
Next
If CancelDel = True Then
MsgBox "Rows 1 - 6 cannot be deleted."
Else
Target.EntireRow.Delete
End If
Worksheets(1).Protect ("support")
End If
Application.ScreenUpdating = True

End Sub

InputBox method with range
 

With the error handler in place, the selected range simply does not get
deleted. Stepping through code, when I get to "If Target Is Nothing..." and
place my mouse pointer over Target, I see "Object variable or With not set".

When I comment out the error handler, I get the 424 error: Object required,
as if I had cancelled the inputbox, which I didn't.

"stevebriz" wrote:

Emily
when you say"It is also inconsistent - sometimes a chosen range is
recognized, and sometimes the same range is not recognized."
Do mean that 1) the input does not come up with the activecell.address
or 2)you type the range in the input box and it just does not delete?

Try disabling the on error resume next and see where you are getting
the error? this might gives some idea of what is going on.

Emily Edgington wrote:
I am trying to use an InputBox method to ask the user for a range of rows.
It works most of the time, but sometimes returns nothing, even though a range
was chosen. It is also inconsistent - sometimes a chosen range is
recognized, and sometimes the same range is not recognized. What gives?
(Excel 2000 on Windows XP)

Sub Delete_Rows()

Dim Target As Range
Dim SpecialRows() As Integer
Dim i As Integer
Dim BadRowsList As String
Dim CancelDel As Boolean
Dim EndCleared As Range

CancelDel = False

On Error Resume Next
Title = " "
Prompt = "Select row (or range of rows) to be deleted."

Set Target = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8)
If Target Is Nothing Then
Exit Sub
Else
Application.ScreenUpdating = False
ReDim SpecialRows(Target.Rows.Count - 1) As Integer
i = 0
Worksheets(1).Unprotect ("support")
For Each rw In Target.Rows
If rw.Row < 7 Then
SpecialRows(i) = rw.Row
i = i
CancelDel = True
End If
Next
If CancelDel = True Then
MsgBox "Rows 1 - 6 cannot be deleted."
Else
Target.EntireRow.Delete
End If
Worksheets(1).Protect ("support")
End If
Application.ScreenUpdating = True

End Sub

InputBox method with range
 

Also, even when it does work, when stepping through the code, Target reflects
the value within the cell (if only one cell is chosen), rather than the
range. I have dimmed Target as a range and used "set" and "type 8".

"Emily Edgington" wrote:

With the error handler in place, the selected range simply does not get
deleted. Stepping through code, when I get to "If Target Is Nothing..." and
place my mouse pointer over Target, I see "Object variable or With not set".

When I comment out the error handler, I get the 424 error: Object required,
as if I had cancelled the inputbox, which I didn't.

"stevebriz" wrote:

Emily
when you say"It is also inconsistent - sometimes a chosen range is
recognized, and sometimes the same range is not recognized."
Do mean that 1) the input does not come up with the activecell.address
or 2)you type the range in the input box and it just does not delete?

Try disabling the on error resume next and see where you are getting
the error? this might gives some idea of what is going on.

Emily Edgington wrote:
I am trying to use an InputBox method to ask the user for a range of rows.
It works most of the time, but sometimes returns nothing, even though a range
was chosen. It is also inconsistent - sometimes a chosen range is
recognized, and sometimes the same range is not recognized. What gives?
(Excel 2000 on Windows XP)

Sub Delete_Rows()

Dim Target As Range
Dim SpecialRows() As Integer
Dim i As Integer
Dim BadRowsList As String
Dim CancelDel As Boolean
Dim EndCleared As Range

CancelDel = False

On Error Resume Next
Title = " "
Prompt = "Select row (or range of rows) to be deleted."

Set Target = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8)
If Target Is Nothing Then
Exit Sub
Else
Application.ScreenUpdating = False
ReDim SpecialRows(Target.Rows.Count - 1) As Integer
i = 0
Worksheets(1).Unprotect ("support")
For Each rw In Target.Rows
If rw.Row < 7 Then
SpecialRows(i) = rw.Row
i = i
CancelDel = True
End If
Next
If CancelDel = True Then
MsgBox "Rows 1 - 6 cannot be deleted."
Else
Target.EntireRow.Delete
End If
Worksheets(1).Protect ("support")
End If
Application.ScreenUpdating = True

End Sub

InputBox method with range
 

I have tried you macro and it works perfectly...no problem..(office
2003) with or without the error handler.
try changing the word target to something else I am worried that
Target defined elsewhere
let us know how you get on..

InputBox method with range
 

Does this sound like a situation you have on your worksheet:

http://www.jkp-ads.com/Articles/SelectARange.asp

--
Regards,
Tom Ogilvy


"Emily Edgington" wrote in
message ...
Thanks, Tom, but I can still replicate the problem with that code. I have
determined that I consistently have the issue when I select all rows below
row 6. For example, my spreadsheet may have values from A7:E40, and I can
select A10:E12 for deletion and it works, but if I choose the entire block
(A7:E40), my Target value equals Nothing, and no deletion occurs.

"Tom Ogilvy" wrote:

Try it this way:

Sub Delete_Rows()

Dim Target As Range
Dim SpecialRows() As Integer
Dim i As Integer
Dim BadRowsList As String
Dim CancelDel As Boolean
Dim EndCleared As Range

CancelDel = False


Title = " "
Prompt = "Select row (or range of rows) to be deleted."
On Error Resume Next
Set Target = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8)
On Error goto 0
If Target Is Nothing Then
Exit Sub
Else
Application.ScreenUpdating = False
ReDim SpecialRows(Target.Rows.Count - 1) As Integer
i = 0
Worksheets(1).Unprotect ("support")
For Each rw In Target.Rows
If rw.Row < 7 Then
SpecialRows(i) = rw.Row
i = i
CancelDel = True
End If
Next
If CancelDel = True Then
MsgBox "Rows 1 - 6 cannot be deleted."
Else
Target.EntireRow.Delete
End If
Worksheets(1).Protect ("support")
End If
Application.ScreenUpdating = True

End Sub

--
Regards,
Tom Ogilvy

"Emily Edgington" wrote:

Actually, I did change it after the original posting - from Target to
myRange, and I still have the issue.

"stevebriz" wrote:

I have tried you macro and it works perfectly...no problem..(office
2003) with or without the error handler.
try changing the word target to something else I am worried that
Target defined elsewhere
let us know how you get on..

InputBox method with range
 

With the link Tom has since posted.. which I sent you early yesterday
evening I modified the code in your spreadsheet you sent me with the
method used in that link..
I emailed your spreadsheet back with the changes.
You should be good to go now!