Delete last row from range
 

Hi,

I have code as follows,

Dim DataRow As Long
Application.ScreenUpdating = False
DataRow = ProjectsListBox.ListIndex + 1
With ThisWorkbook.Worksheets("Data")
Rng.Rows(DataRow).Delete xlShiftUp
' If listbox entries have all been deleted, reset the 'Rng'
object _
' so that a valid rowsource can be updated.
If Rng Is Nothing Then
Set Rng = .Range("S2").Resize(, 2)
Else
Set Rng = .Range("S2").Resize(Rng.Rows.Count, 2)
End If
ProjectsListBox.RowSource = Rng.Address(External:=True)
ProjectsListBox.ListIndex = -1
End With

My problem is with my set 'Rng'. If the last row of the range object
'Rng'
is deleted, "Rng Is Nothing" always returns false and then errors. Rng
is
initially set to a worksheet range and works well until the last entry
is deleted.
Any suggestions or alternatives?

Regards,

Stefano

Delete last row from range
 

Here's some test code you could try

Dim Rng As Range
Dim myRange As Range
Set Rng = Range("A2:Z200")
Debug.Print Rng.Row, Rng.Rows.Count
Set myRange = Cells(Rng.Row, Rng.Column).Offset(Rng.Rows.Count - Rng.Row +
1, 0)
myRange.EntireRow.Delete

--
HTH,
Barb Reinhardt



" wrote:

Hi,

I have code as follows,

Dim DataRow As Long
Application.ScreenUpdating = False
DataRow = ProjectsListBox.ListIndex + 1
With ThisWorkbook.Worksheets("Data")
Rng.Rows(DataRow).Delete xlShiftUp
' If listbox entries have all been deleted, reset the 'Rng'
object _
' so that a valid rowsource can be updated.
If Rng Is Nothing Then
Set Rng = .Range("S2").Resize(, 2)
Else
Set Rng = .Range("S2").Resize(Rng.Rows.Count, 2)
End If
ProjectsListBox.RowSource = Rng.Address(External:=True)
ProjectsListBox.ListIndex = -1
End With

My problem is with my set 'Rng'. If the last row of the range object
'Rng'
is deleted, "Rng Is Nothing" always returns false and then errors. Rng
is
initially set to a worksheet range and works well until the last entry
is deleted.
Any suggestions or alternatives?

Regards,

Stefano

Delete last row from range
 

On Sep 7, 7:56 pm, Barb Reinhardt
wrote:
Here's some test code you could try

Dim Rng As Range
Dim myRange As Range
Set Rng = Range("A2:Z200")
Debug.Print Rng.Row, Rng.Rows.Count
Set myRange = Cells(Rng.Row, Rng.Column).Offset(Rng.Rows.Count - Rng.Row +
1, 0)
myRange.EntireRow.Delete

--
HTH,
Barb Reinhardt



" wrote:
Hi,

I have code as follows,

Dim DataRow As Long
Application.ScreenUpdating = False
DataRow = ProjectsListBox.ListIndex + 1
With ThisWorkbook.Worksheets("Data")
Rng.Rows(DataRow).Delete xlShiftUp
' If listbox entries have all been deleted, reset the 'Rng'
object _
' so that a valid rowsource can be updated.
If Rng Is Nothing Then
Set Rng = .Range("S2").Resize(, 2)
Else
Set Rng = .Range("S2").Resize(Rng.Rows.Count, 2)
End If
ProjectsListBox.RowSource = Rng.Address(External:=True)
ProjectsListBox.ListIndex = -1
End With

My problem is with my set 'Rng'. If the last row of the range object
'Rng'
is deleted, "Rng Is Nothing" always returns false and then errors. Rng
is
initially set to a worksheet range and works well until the last entry
is deleted.
Any suggestions or alternatives?

Regards,

Stefano- Hide quoted text -


Thanks for the test code Barb, however, my test at "If Rng Is Nothing
Then" still
puzzles me. It always returns false possibly letting me know that I am
using a poor
construct. I guess I could use "If Rng.Rows.Count = 1 Then" .....

Regards,

Stefano

Delete last row from range
 

I believe that if these is nothing in the range, the rows.count part won't
work. Think about it.
--
HTH,
Barb Reinhardt



" wrote:

On Sep 7, 7:56 pm, Barb Reinhardt
wrote:
Here's some test code you could try

Dim Rng As Range
Dim myRange As Range
Set Rng = Range("A2:Z200")
Debug.Print Rng.Row, Rng.Rows.Count
Set myRange = Cells(Rng.Row, Rng.Column).Offset(Rng.Rows.Count - Rng.Row +
1, 0)
myRange.EntireRow.Delete

--
HTH,
Barb Reinhardt



" wrote:
Hi,

I have code as follows,

Dim DataRow As Long
Application.ScreenUpdating = False
DataRow = ProjectsListBox.ListIndex + 1
With ThisWorkbook.Worksheets("Data")
Rng.Rows(DataRow).Delete xlShiftUp
' If listbox entries have all been deleted, reset the 'Rng'
object _
' so that a valid rowsource can be updated.
If Rng Is Nothing Then
Set Rng = .Range("S2").Resize(, 2)
Else
Set Rng = .Range("S2").Resize(Rng.Rows.Count, 2)
End If
ProjectsListBox.RowSource = Rng.Address(External:=True)
ProjectsListBox.ListIndex = -1
End With

My problem is with my set 'Rng'. If the last row of the range object
'Rng'
is deleted, "Rng Is Nothing" always returns false and then errors. Rng
is
initially set to a worksheet range and works well until the last entry
is deleted.
Any suggestions or alternatives?

Regards,

Stefano- Hide quoted text -


Thanks for the test code Barb, however, my test at "If Rng Is Nothing
Then" still
puzzles me. It always returns false possibly letting me know that I am
using a poor
construct. I guess I could use "If Rng.Rows.Count = 1 Then" .....

Regards,

Stefano