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

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

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

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

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..